MarkVMcCullagh
Board Regular
- Joined
- Oct 22, 2002
- Messages
- 72
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
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