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!
ITCert = ThisWorkbook.Sheets("IT Certification")
business makes sense. I would suggest something more likeIf Me.CheckBox1 Then
(copy "IT Certification" sheet)
End If
If Me.CheckBox2 Then`(copy "Business Skills & Productivity" sheet)
End If
, …. You could improve your question by explaining what you mean by “combine”.