This script initializes the destination sheet then combines data from certain sheets (and not others)

combine_certain_sheets.vb

Option Explicit
Public Sub CombineCertainSheets()
    
    Dim wks As Worksheet, wksDst As Worksheet
    Dim strName As String
    Dim lngSrcLastRow As Long, lngDstLastRow As Long, _
        lngLastCol As Long
    Dim rngSrc As Range, rngDst As Range, rngToClear As Range
    
    'Set references up-front
    Set wksDst = ThisWorkbook.Worksheets("ALPHA")
    lngLastCol = LastOccupiedColNumInRow(wksDst, 1)
    
    'Identify the last-occupied row on the ALPHA sheet, clearing
    'any pre-existing values that are not headers
    lngDstLastRow = LastOccupiedRowNumInCol(wksDst, 1)
    If lngDstLastRow > 1 Then
        With wksDst
            .Range(.Cells(2, 1), .Cells(lngDstLastRow, lngLastCol)).ClearContents
        End With
    End If
    
    'Now that the destination has been initialized, set the
    'first destination range to row 2, column A
    Set rngDst = wksDst.Cells(2, 1)
    
    'Loop through all of the worksheets in this workbook, skipping
    'the names we know we DO NOT want to combine into ALPHA
    For Each wks In ThisWorkbook.Worksheets
        
        'If this sheet name is NOT one that we want to ignore,
        'append the data to the ALPHA sheet
        strName = UCase(wks.Name)
        If strName <> "1ST ORIG" And _
           strName <> "BUSIEST DAY" And _
           strName <> "MASTER" And _
           strName <> "ALPHA" And _
           strName <> "ARINC" And _
           strName <> "ARINC ARRIVAL" Then
            
            'Yay! If we have gotten here, then this is one of the
            'numbered sheets that we want to combine!
            
            'Identify the last occupied row on this sheet
            'by looking in column A
            lngSrcLastRow = LastOccupiedRowNumInCol(wks, 1)
            
            'Store the source data then copy the values and number formats,
            'followed by the formulas
            With wks
                Set rngSrc = .Range(.Cells(7, 1), .Cells(lngSrcLastRow, lngLastCol))
                rngSrc.Copy
                rngDst.PasteSpecial xlPasteValuesAndNumberFormats
                rngDst.PasteSpecial xlPasteFormulas
            End With
            
            'Redefine the destination range now that new data has been added
            lngDstLastRow = LastOccupiedRowNumInCol(wksDst, 1)
            Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)
            
        End If
        
    Next wks
    
    'Let the user know our macro is complete!
    MsgBox "Sheets combined!"
    
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'INPUT       : Sheet, the worksheet we'll work on. ColNum, the number of the
'            : column we'd like to find the last occupied row in
'OUTPUT      : Long, the last occupied row in the column
'SPECIAL CASE: if ColNum is <= 0, return 0 (error condition)
Public Function LastOccupiedRowNumInCol(Sheet As Worksheet, _
                                        ColNum As Long) As Long
    Dim lng
    If ColNum > 0 Then
        With Sheet
            lng = .Cells(.Rows.Count, ColNum).End(xlUp).Row
        End With
    Else
        lng = 0
    End If
    LastOccupiedRowNumInCol = lng
End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'INPUT       : Sheet, the worksheet we'll work on. RowNum, the number of the
'            : row we'd like to find the last-occupied column in
'OUTPUT      : Long, the last occupied column in the row
'SPECIAL CASE: if RowNum <= 0, return 0 (error condition)
Public Function LastOccupiedColNumInRow(Sheet As Worksheet, _
                                        RowNum As Long) As Long
    Dim lng As Long
    If RowNum > 0 Then
        With Sheet
            lng = .Cells(RowNum, .Columns.Count).End(xlToLeft).Column
        End With
    Else
        lng = 0
    End If
    LastOccupiedColNumInRow = lng
End Function

Hi there,

I used this code which worked well, however, sheets with vba codes (that I do not want the data combined) gets included. Do you know what's happening there? Thank you!

Regards,
Tin