Need to modify VBA Code that copies multiple sheets to a master sheet

Hello AXGirl,

You can approach this problem in two ways. Either examine a list of sheet names you want to use or examine a list of sheet names to exclude. The choice really depends on which list is shorter. Generally, the exclusion list is shorter.

Private Sub Worksheet_Activate()

    Dim Sheet As Worksheet

    For Each Sheet In Me.Parent.Sheets
        Select Case Sheet.Name
            Case Is = "Master", Me.Name
                ' Do Nothing - These are the sheets you want to skip
            Case Else
                If Sheet.Cells(Rows.Count, 1).End(xlUp).Row <> 1 Then
                    Sheet.Range(Sheet.Cells(2, 1), Sheet.Cells(Sheet.Cells(Rows.Count, 1).End(xlUp).Row, 18)).Copy Destination:=Me.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
                Else
                    Me.Range(Cells(2, 1), Cells(Rows.Count, 18)).Clear
                End If
        End Select
    Next Sheet
    
End Sub

Hello AXGirl,

Switch the statements around like this...

Private Sub Worksheet_Activate()

    Dim Sheet As Worksheet

    For Each Sheet In Me.Parent.Sheets
        Select Case Sheet.Name
            Case Is = "A", "C", "E"
                If Sheet.Cells(Rows.Count, 1).End(xlUp).Row <> 1 Then
                    Sheet.Range(Sheet.Cells(2, 1), Sheet.Cells(Sheet.Cells(Rows.Count, 1).End(xlUp).Row, 18)).Copy Destination:=Me.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
                Else
                    Me.Range(Cells(2, 1), Cells(Rows.Count, 18)).Clear
                End If
            Case Else
                ' Do Nothing - These are the sheets you want to skip
        End Select
    Next Sheet
    
End Sub

Hello AXGirl,

You're welcome. Glad to help a fellow Tarheel.