none
Combining Worksheets...omit one specific from being combined. RRS feed

  • Question

  • I have a workbook that combines data from other worksheets to make one named Combined.

    I would like to make a "Legends" sheet that would be omitted from the macro.

    Code I have that works:

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

        On Error Resume Next
        Sheets(1).Select
        Worksheets.Add ' add a sheet in first place
        Sheets(1).Name = "Combined"

        ' copy headings
        Sheets(2).Activate
        Range("A1").EntireRow.Select
        Selection.Copy Destination:=Sheets(1).Range("A1")

        For Each s In ActiveWorkbook.Sheets
            If s.Name <> "Combined" Then
                Application.GoTo Sheets(s.Name).[a1]
                Selection.CurrentRegion.Select
                ' Don't copy the headings
                Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
                Selection.Copy Destination:=Sheets("Combined"). _
                  Cells(Rows.Count, 1).End(xlUp)(2)
            End If
        Next
    End Sub

     

    Monday, October 10, 2016 11:27 PM

Answers

  • Hi,

    Add an If condition to check if name equals to the specific one.

     For Each s In ActiveWorkbook.Sheets
             If s.Name <> "Combined" Then
                If s.Name <> "Legends" Then
                    Application.GoTo Sheets(s.Name).[a1]
                    Selection.CurrentRegion.Select
                    ' Don't copy the headings
                    Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
                    Selection.Copy Destination:=Sheets("Combined"). _
                      Cells(Rows.Count, 1).End(xlUp)(2)
                End If
             End If
         Next

    Tuesday, October 11, 2016 2:46 AM
    Moderator