Compiling 2 different sets of data from multiple sheets into 2 master sheets

[Ay2Cee2]

Hello all,

I am a VBA newbie and I am currently trying to get my head round a code that will copy and paste data from 62 sheets into 2 master sheets within my workbook, each separate sheet has a variable amount of rows that will need to be transferred. There are 2 different data sets I am trying to compile into the master sheets but every string of VBA code I use essentially tells me that I'm in over my head. Would really appreciate any help you guys may be able to offer

 

[Ay2Cee2]

Private Sub CommandButton2_Click()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim LastRow As Long
    Dim shLastRow As Long
    Dim CopyRng As Range
    Dim StartRow As Long
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    'Delete the sheet "Handover Compilation" if it exist
    Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Worksheets("Handover Compilation").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    'Add a worksheet with the name "Handover Compilation"
    Set DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "Handover Compilation"
    'Fill in the start row
    StartRow = 2
    'loop through all worksheets and copy the data to the DestSh
    For Each sh In ActiveWorkbook.Worksheets
        'Loop through all worksheets except the RDBMerge worksheet and the
        'Information worksheet, you can ad more sheets to the array if you want.
        If IsError(Application.Match(sh.Name, _
                                     Array(DestSh.Name, "SL Compilation", "Handover Compilation", "Control Buttons", "SMART Locate Buckinghamshire", "SMART Locate Stevenage", "SMART Locate Cambridge", "SMART Locate Suffolk", "SMART Locate Durham", "SMART Locate York N", "SMART Locate Cumbria", "SMART Locate Lancashire", "SMART Locate Derbyshire", "SMART Locate Leicestershire", "SMART Locate Dudley, Walsall", "SMART Locate Stoke, Stafford", "SMART Locate Essex North", "SMART Locate Essex South", "SMART Locate Hertfordshire", "SMART Locate London", "SMART Locate Lincolnshire", "SMART Locate Notts Trent", "SMART Locate Lincs South", "SMART Locate Northants", "SMART Locate Liv, Warr", "SMART Locate Wales N", "SMART Locate Manc N", "SMART Locate Manc S", "SMART Locate Norfolk East", "SMART Locate Norfolk West", "SMART Locate Wales South", "SMART Locate Worcs Herefs Glos", "SMART Locate West Midlands", "SMART Locate Yorks S", "SMART Locate Yorks W"), 0)) Then
            'Find the last row with data on the DestSh and sh
            Last = LastRow(DestSh)
            shLast = LastRow(sh)
            'If sh is not empty and if the last row >= StartRow copy the CopyRng
            If shLast > 0 And shLast >= StartRow Then
                'Set the range that you want to copy
                Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))
                '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
                'This example copies values/formats, if you only want to copy the
                'values or want to copy everything look below example 1 on this page
                CopyRng.Copy
                With DestSh.Cells(Last + 1, "A")
                    .PasteSpecial xlPasteValues
                    .PasteSpecial xlPasteFormats
                    Application.CutCopyMode = False
                End With
            End If
        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

Here is an example of one of the codes I have tried