VBA: Consolidate Data Sheets Except for One

[unknownymous]

Hello Gurus,

I'm currently working on a project and using below VBA codes.

Sub Consolidate

Application.ScreenUpdating = False
Dim LastRow As Long, ws As Worksheet, desWS As Worksheet
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If Not Evaluate("isref('" & "Merge" & "'!A1)") Then
Sheets.Add(before:=Sheets(1)).Name = "Merge"
Range("A1").Resize(, 5) = Array("Data1", "Data2", "Data3", "Data4", "Data5")
'Columns.AutoFit
Else
Sheets("Merge").UsedRange.Offset(1).ClearContents
End If
Set desWS = Sheets("Merge")
For Each ws In Sheets
If ws.Name <> "Merge" Then
With ws
LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
.Range("A1:Z" & LastRow).Copy desWS.Cells(desWS.Rows.Count, "B").End(xlUp).Offset(1)
desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1).Resize(LastRow - 1) = ws.Name
End With
End If
Next ws
Application.ScreenUpdating = True

End Sub


Note: I have 6 tabs namely: Raw, Data 1, Data 2, Data 3, Data 4, Data 5 and Merge. I need to consolidate the 5 Data tabs then dump it all in the Merge Tab. Once done, these 5 data tabs should be deleted. I noticed that when running the macro, the Raw tab is being added on the Merge tab as well - I want it to be excluded.


Appreciate the help in tweaking the code.