Adjusting VB code to combine only certain worksheets

[Sashalein]

I saw the below code in another thread that does almost what I need it to do. The only thing is that I need to only select certain worksheets, not all. Is there an adjustment I can make to this code or is there something I can do differently?

Each worksheet has a table on it as well, is there a code I could use to just combine certain tables?

Sub debit1()
'Combine all worksheets to the Summary sheet
'Created by Trevor G 30 June 2011
Dim ws As Worksheet
Dim wsSummary As Worksheet
Set wsSummary = Worksheets("Summary")
For Each ws In Sheets
If ws.Name <> "Summary" Then
ws.Activate
Range("a1").Select
Selection.CurrentRegion.Select
' Selects the current data area without the top row.
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
' Selects the visible cells of the selection.
' This is useful if data is filtered or contains hidden rows.
Selection.SpecialCells(xlVisible).Copy
wsSummary.Activate
Range("A1").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.PasteSpecial xlPasteAll
End If
Next

End Sub

 

[Sashalein]

Basically I need to exclude the worksheets "All Data" and "Manager Detail", I'm not sure what adjustment I would need to make.

Sub debit1()
'Combine all worksheets to the Summary sheet
'Created by Trevor G 30 June 2011
Dim ws As Worksheet
Dim wsSummary As Worksheet
Set wsSummary = Worksheets("Summary")
For Each ws In Sheets
If ws.Name <> "Summary" Then
ws.Activate
Range("a1").Select
Selection.CurrentRegion.Select
' Selects the current data area without the top row.
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
' Selects the visible cells of the selection.
' This is useful if data is filtered or contains hidden rows.
Selection.SpecialCells(xlVisible).Copy
wsSummary.Activate
Range("A1").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.PasteSpecial xlPasteAll
End If
Next

End Sub

 

[jbridsworth]

I know a few ways of doing what you want. The first 2 methods assume you know the names of your tabs, the third that all your tabs are side by side with no extras in between.

1. With Workbooks(compiler)
.Sheets(Array("sheet1", "sheet2", "sheet3")).range("A1").select
'the rest of your code here
End With

2. Sheets(Array("sheet1", "sheet2", "sheet3")).select
'code here

3. Dim ivar as variant 'ivar is just a variable, you can give it any name you like
Sheets("sheet0").Activate 'Activate the sheet before the first one you want to change, .Select should work as well.
For ivar = 1 to 3 '3 being the number of sheets
ActiveSheet.Next.Select
'code to make changes here
Next ivar

 

[jbridsworth]

Sorry, with my first example - compiler is a variable I used, I had defined it using the below code earlier in one of my macros

Dim compiler As Variant
compiler = ThisWorkbook.Name

 

[Sashalein]

Thank you for responding!

I do not have Workbooks(compiler) so I can't use the first one.

I could not get the second or 3rd options to work... Do I have to change anything in the code to use them?

Example of how I did option 2:

Sub debit1()'Combine all worksheets to the Summary sheet
'Created by Trevor G 30 June 2011
Sheets(Array("sheet1", "sheet2", "sheet3")).select
Dim ws As Worksheet
Dim wsSummary As Worksheet
Set wsSummary = Worksheets("Summary")
For Each ws In Sheets
If ws.Name <> "Summary" Then
ws.Activate
Range("a1").Select
Selection.CurrentRegion.Select
' Selects the current data area without the top row.
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
' Selects the visible cells of the selection.
' This is useful if data is filtered or contains hidden rows.
Selection.SpecialCells(xlVisible).Copy
wsSummary.Activate
Range("A1").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.PasteSpecial xlPasteAll
End If
Next


End Sub

It is still pulling all of the worksheets.

 

[jbridsworth]

I'm not sure how you've defined CurrentRegion, so I can't test the full macro.

Assuming that Summary is your first sheet, you could try this:

Sub debit1()
Dim ivar As Variant
Sheets("Summary").Activate
For ivar = 1 To 3 'change 3 to however many sheets you need to change
    ActiveSheet.Next.Select
    Range("A1").Select
    Selection.currentregion.Select
    Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
    Selection.SpecialCells(xlVisible).Copy
    Range("A1").Select
    Do Until ActiveCell.Value = ""
        ActiveCell.Offset(1, 0).Select
    Loop
    ActiveCell.PasteSpecial xlPasteAll
Next ivar
End Sub

 

[Sashalein]

Is there a way I can share my spreadsheet with you so you can see it?

 

[jbridsworth]

It appears that you can get access to post your workbook as an attachment, but I don't have access & don't know how you would go about getting it.
I suggest coding your macro for 2 specific sheets & test it to make sure it works properly, then add in the code for multiple sheets. I've never worked with the tables feature, so I don't know if there are special considerations you have to account for.

FYI - with my first example, simply replace compiler</SPAN> with "workbookname"</SPAN> where workbookname is the name of your excel file. With most situations in VBA, when you are referring to something (sheets, workbooks, cells, ranges, etc.), you can either address them specifically using quotation marks (i.e. Range("A1").Select), or obtain the information from somewhere else & assign the information as a variable & use the variable name without quotation marks, i.e.:

Dim variable1 As Variant
variable1 = Range("A1").Address
Range(variable1).Select

Each method has its own benefits vs. the other method, it all depends on what you are doing.</SPAN>