Consolidating Worksheets with VBA

Hi Everyone,

I am working on a project for fun but I am struggling to consolidate a few worksheets (not all worksheets) with VBA into a master worksheet.

The idea is to copy all data over from tabs with "ABC" in the worksheet name, i.e. copy from "ABC 1", "ABC 2", "ABC A" but not "XYZ".

All the ABC worksheets have the same header, so I would like to retain this only once / the first time when consolidating the data.

If anyone can figure out the code, that would be really helpful!

Thanks!

I have come across that site, however, the code I copied and altered does not seem to show an output in the "Master ABC" tab.

This is what I have so far. Any thoughts on where it is going wrong or how to fix it?

Thanks!

Module 1

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Module 2

Sub Consolidate_ABC()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long
Dim CopyRng As Range
Dim StartRow As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "Master ABC" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("Master ABC").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "Master ABC"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "Master ABC"

'Fill in the start row
StartRow = 2

'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets
If LCase(Left(sh.Name, 4)) = "ABC " Then

'Find the last row with data on the DestSh
Last = LastRow(DestSh)

'Fill in the range that you want to copy
Set CopyRng = sh.Range("A1").CurrentRegion

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

'Copy header row, change the range if you use more columns
If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
sh.Range("A1:AZ1").Copy DestSh.Range("A1")
End If

'This example copies values/formats, if you only want to copy the
'values or want to copy everything look at the example below this macro
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

End If
Next

ExitTheSub:

Application.Goto DestSh.Cells(1)

'AutoFit the column width in the DestSh sheet
DestSh.Columns.AutoFit

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub