How can I copy from specific sheet in excel

Question

I used the following VBA code to try to consolidate numerous pages into one sheet:

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

    On Error Resume Next
    Sheets("Operational").Activate
    Range("A1:A2").EntireRow.Select
    Selection.Copy Destination:=Sheets("Combined").Range("A1:A2")

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

The document has numerous tabs, thus all I need to do is unite 4 sheets into one called (Combain). The last document was copied three times, which is the problem. Is there a remedy for that?

Answer 1

You should use wb.Sheets("Name of sheet").Copy - best to be explicit about the workbook you're copying from. Plus if you're copying from more than one file you can't have two sheets with the same name in the destination workbook.