excel vba merge multiple sheets from multiple workbooks into one workbook

Question

I have two excel workbooks and I need to take a set of sheets from one and a set of sheets form another and save it as a new workbook. Since I will be doing this weekly, I would like to save it as a macro/vba.

I found this code online and edited it, but it is not working.

Sub CopySheets()
    Dim wkb As Workbook
    Dim sWksName As String

sWksName = "Store 1"
For Each wkb In Workbooks
    If wkb.Name <> ThisWorkbook.Name Then
        wkb.Worksheets(sWksName).Copy _
          Before:=ThisWorkbook.Sheets(1)
    End If
Next
Set wkb = Nothing

    sWksName = "Store 3"
For Each wkb In Workbooks
    If wkb.Name <> ThisWorkbook.Name Then
        wkb.Worksheets(sWksName).Copy _
          Before:=ThisWorkbook.Sheets(1)
    End If
Next
Set wkb = Nothing

    sWksName = "Store 30"
For Each wkb In Workbooks
    If wkb.Name <> ThisWorkbook.Name Then
        wkb.Worksheets(sWksName).Copy _
          Before:=ThisWorkbook.Sheets(1)
    End If
Next
Set wkb = Nothing

    sWksName = "Store 33"
For Each wkb In Workbooks
    If wkb.Name <> ThisWorkbook.Name Then
        wkb.Worksheets(sWksName).Copy _
          Before:=ThisWorkbook.Sheets(1)
    End If
Next
Set wkb = Nothing


End Sub

I have to have both workbooks open, which is no problem. The sheet "Store 1" gets copied fine and then it stops and when I click on debug, it tells me that there is an error with this line

 wkb.Worksheets(sWksName).Copy _
              Before:=ThisWorkbook.Sheets(1)

Error message: "Script out of range"

Is there a worksheet named "Store 1" in every open workbook (except the destination)? If not, it won't be able to find that worksheet in the next workbook it looks in and cause the failure. — tigeravatar
no, it's only in one workbook and not the other - I see, it's looking for the same sheet in every workbook! How can I change it to just get one worksheet from one workbook and another worksheet from another workbook? — jeangelj
Instead of doing a For Each wkb to loop through the workbooks, you'll need to specify which workbook has which sheet. If you won't know the names beforehand, you'll have to keep using the For Each loop, but put a check in to make sure the workbook contains the sheet you're trying to copy. If it does contain the sheet, copy it, otherwise skip — tigeravatar
I am very new to VBA, do you have any example of code or a good website to look the information up? Thank you very mcuh — jeangelj

tigeravatar tigeravatar · Accepted Answer · 2017-08-08T13:21:43

Sub CopySheets()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim sWsNames As String

    sWsNames = "Store 1,Store 3,Store 30,Store 33"

    For Each wb In Workbooks
        If wb.Name <> ThisWorkbook.Name Then
            For Each ws In wb.Sheets
                If InStr(1, "," & sWsNames & ",", "," & ws.Name & ",", vbTextCompare) > 0 Then
                    ws.Copy Before:=ThisWorkbook.Sheets(1)
                End If
            Next ws
        End If
    Next

End Sub

excel vba merge multiple sheets from multiple workbooks into one workbook

1 Answers