1

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!

4
  • I read the first two paragraphs of your question and I thought to myself, “Sure, that sounds reasonable.”  Then I read “The worksheets are … and … I'm trying to store them in variables”, and I started scratching my head.  It sounds like what you want to do is create a fourth worksheet, and then copy some or all of the first three into the fourth. – Scott Mar 10 '19 at 18:22
  • Hi Scott, sorry that I was unclear. You are exactly correct, I'm trying to copy all of the worksheets selected by the check boxes into a new worksheet (this would not be all the worksheets in the workbook, but only those that have been selected). – dagda13 Mar 10 '19 at 18:30
  • Well, then, I’m not sure that this ITCert = ThisWorkbook.Sheets("IT Certification") business makes sense. I would suggest something more like If 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”. – Scott Mar 10 '19 at 18:40
  • Thanks, Scott, will do. – dagda13 Mar 10 '19 at 18:52
0

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
3
  • 1
    Thanks very much, busybite, the above code works except for one problem. Only the first row of each sheet get combined into the new sheet, so there a total of 2 rows on the new sheet and not a total of all the rows. I suppose the "r" variable is set to "2", but each sheet has a different number of rows. – dagda13 Mar 11 '19 at 22:55
  • You're welcome. Range A1 to E(r=2) has been copied. I edited the code to find the last row in column A. Take care that there are no empty rows between the source-rows. – busybyte Mar 11 '19 at 23:56
  • Thanks again. One quick question, and I know this is beyond the original question I asked. Do you know if it’s possible to delete rows that have duplicates on a single column, say, column D, so there would only be rows that have unique values in column D? – dagda13 Mar 12 '19 at 23:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.