Combine all sheets into one except for one

[jmazorra]

Hello:

I have the below statement that combines all worksheets into one. Essentially, it combines all into one called "Combined Reports". However, I would like to rewrite the sub so that a sheet called "emailList" is not combined into the report.

Any help will be appreciated.

Sub combine_all_Reports()



    Dim J As Integer
    Dim s As Worksheet


    On Error Resume Next


    ' copy headings
    Sheets(2).Activate
    Range("B8").EntireColumn.Select
    Selection.Copy Destination:=wksCombinedReports.Range("B9")


    For Each s In ActiveWorkbook.Sheets
        If s.Name <> "Combined Reports" Then
            Application.GoTo Sheets(s.Name).[B1]
            Selection.CurrentRegion.Select
             Selection.Copy Destination:=Sheets("Combined Reports"). _
              Cells(Rows.Count, 1).End(xlUp)(2)
              wksCombinedReports.Cells.EntireColumn.AutoFit


        End If
    Next
    
    End Sub

 

[tonyyy]

jmazzora,

You might consider the following tweak...

Sub combine_all_Reports()
    Dim J As Integer
    Dim s As Worksheet

    On Error Resume Next

    ' copy headings
    Sheets(2).Activate
    Range("B8").EntireColumn.Select
    Selection.Copy Destination:=wksCombinedReports.Range("B9")

    For Each s In ActiveWorkbook.Sheets
        [COLOR=#ff0000]If s.Name <> "Combined Reports" And s.Name <> "emailList" Then[/COLOR]
            Application.GoTo Sheets(s.Name).[B1]
            Selection.CurrentRegion.Select
            Selection.Copy Destination:=Sheets("Combined Reports"). _
              Cells(Rows.Count, 1).End(xlUp)(2)
            wksCombinedReports.Cells.EntireColumn.AutoFit
        End If
    Next
End Sub

Cheers,

tonyyy