Consolidating dynamic no. of worksheets & excluding some

[MarkVMcCullagh]

I have a workbook that will contain 20-35 worksheets that will need consolidating into 1 worksheet called "Total" within the same workbook. The worksheets will all have an identical layout but the number of worksheets needing consolidating will vary upwards or downwards.

However there will be several worksheets within the workbook which I do not want consolidated named "ExcludeMe1", "ExcludeMe2" and "ExcludeMe3".

I found the following code on the site and was wondering how I could modify it to exclude certain worksheets.

Sub CombineData()
' Written by Barrie Davidson
Dim WS As Worksheet
Dim Temp As Integer

Application.ScreenUpdating = False
Temp = 1
For Each WS In ActiveWorkbook.Sheets
If WS.Name <> "Amendments" Then
If Temp = 1 Then
WS.Range("A45:K" & WS.Range("A65536").End(xlUp).Row).Copy _
Destination:=Sheets("Amendments"). _
Range("A5")
Else
WS.Range("A45:K" & WS.Range("A65536").End(xlUp).Row).Copy _
Destination:=Sheets("Amendments"). _
Range("A65536").End(xlUp).Offset(1, 0)
End If
Temp = 0
End If
Next WS
Application.ScreenUpdating = True
End Sub

Thanks in Advance

 

[stanleydgromjr]

Modify this line of code:
If WS.Name <> "Amendments" Then

To this:
If WS.Name <> "Amendments" Or WS.Name <> "ExcludeMe1" Or WS.Name <> "ExcludeMe2" Or WS.Name <> "ExcludeMe3" Then

Have a great day,
Stan

 

[jindon]

Use And, instaed of Or.

 

[stanleydgromjr]

jindon,

Thanks. I ws playing too fast yesterday.

Have a great day,
Stan

 

[MarkVMcCullagh]

Thanks Guys. I've incorporated it already.