• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy and Paste WS from different WB's



I am really struggling to come up with a procedure where I can copy a specific worksheet, from a workbook and paste it into a main workbook, these action will happen approximately for 10 different Workbooks all in the same location.

Any help would be very much appreciated,

Thanks in advance,


I can copy the workbook from one location to another, but I am struggling/can't think on how to pick the specific worksheet and copy it to the main workbook


Excel Ninja
Can You do that manually?
If You can ... then steps with code are basic same
1) Have the main workbook open
2) Open the specific workbook
3) Copy/Paste needed worksheet to Your the main workbook
4) Close the specific workbook
5) Repeat steps from 2 to 4 as many times as needed
6) Remember to save the main workbook too


Thanks, Vletm
After giving some thought I come up with the solution, many thanks for your pointers
Sub MainNew()

    Dim avarArray(2) As Variant
    Dim varItem As Variant
    Dim intIndex As Integer
    Dim objWorkbookOutput As Workbook
    Dim objWorkbookSlave As Workbook
    avarArray(0) = Array("C:\Temp\TestForVitor\Test03.xlsx", "Summary", "Problems")
    avarArray(1) = Array("C:\Temp\TestForVitor\Test02.xlsx", "Detail", "Different")
    avarArray(2) = Array("C:\Temp\TestForVitor\Test01.xlsx", "Summary", "Summary")

    Set objWorkbookOutput = Workbooks.Open("C:\Temp\TestForVitor\Output.xlsb", addtomru:=False)
    For Each varItem In avarArray
        Debug.Print varItem(0), varItem(1)
        Set objWorkbookSlave = Workbooks.Open(varItem(0), ReadOnly:=True, addtomru:=False)
        If varItem(1) <> varItem(2) Then objWorkbookSlave.Sheets(varItem(1)).Name = varItem(2)
        objWorkbookSlave.Sheets(varItem(2)).Copy After:=objWorkbookOutput.Sheets("Front")
        objWorkbookSlave.Close False
    Next varItem
    Application.DisplayAlerts = False
    objWorkbookOutput.SaveAs "C:\Temp\TestForVitor\OutputForCustomer.xlsb"
    Application.DisplayAlerts = True
End Sub
Last edited: