Macro to combine selected workseets using checkboxes

Question

I'm still very new to VBA and I was wondering if what I'm trying to do is even possible.

I have several worksheets in the same workbook and I'm trying to select the worksheets by using check boxes, each of which corresponds to a particular worksheet, and then clicking a button to create a new worksheet with all data from each of the selected separate worksheets. Ie.,:

Worksheet 1:
column1      column2      column3
A            B            C
D            E            F

         +

Worksheet 2:
column1      column2      column3
G            H            I
K            L            O

         =

New Worksheet:
column1      column2      column3
A            B            C
D            E            F
G            H            I
K            L            O

the selected worksheets into one new worksheet. All the worksheets have the same structure (same number of columns, headers, etc). Worksheet 1 and Worksheet 2 above would be those that were selected by a user checking check boxes.

The worksheets are:

• IT Certification,
• Business Skills & Productivity,
• Database and Cybersecurity

and in this case I'm trying to store them in variables depending upon whether the check box that refers to them has been clicked:

Here is the code I have so far:

Private Sub CommandButton1_Click()

Dim ITCert As Worksheet
Dim BSProd As Worksheet
Dim DC As Worksheet

If Me.CheckBox1 = True Then
    ITCert = ThisWorkbook.Sheets("IT Certification")
End If

If Me.CheckBox2 = True Then
    BSProd = ThisWorkbook.Sheets("Business Skills & Productivity")
End If
If Me.CheckBox3 = True Then
    DC = ThisWorkbook.Sheets("Database and Cybersecurity")
End If
End Sub

I'm not quite certain how to combine these sheets. Do I need to create a loop (I'm thinking no because a user would already select the sheets in question by clicking the check boxes). I was thinking of somehow adding the variables I've assigned the worksheets to together, but I've been unable to make this work.

Any help/suggestions would be very much appreciated!

Answer 1

To store objects in variables you have to use the "Set" command.
I.e.:

Set ITCert = ThisWorkbook.Sheets("IT Certification")

A simple copy can look like this, but you should better use the "names" for the source-sheets instead of numbers:
Code edited: Due to your comment !

Option Explicit  'use only clearly defined variables

Public Sub Test()
Dim r As Long
Dim rcnt As Long
Dim Dst As Worksheet
Dim chk1 As Boolean
Dim chk2 As Boolean

With ThisWorkbook
Set Dst = .Sheets.Add(After:=.Worksheets(.Worksheets.Count))

'r = 2
rcnt = 1  ' stores amount of inserted rows
chk1 = True 'simulate checkbox1
chk2 = True 'simulate checkbox2

If chk1 Then
With .Sheets(1)                             
r = .Range("A" & .Rows.Count).End(xlUp).Row 'last source-row
.Range("A1:E" & r).Copy _
Destination:=Dst.Range("A" & rcnt)
rcnt = rcnt + r 'lastrow
End With                                    
End If

If chk2 Then
With .Sheets(2)                             
r = .Range("A" & .Rows.Count).End(xlUp).Row 'last source-row
.Range("A1:E" & r).Copy _
Destination:=Dst.Range("A" & rcnt)
rcnt = rcnt + r 'lastrow
End With                                    
End If

End With
End Sub