• 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

    Hui...

  • 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

Portucale

Member
HI,

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,
 

Portucale

Member
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
 

vletm

Excel Ninja
Portucale
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
 

Portucale

Member
Thanks, Vletm
After giving some thought I come up with the solution, many thanks for your pointers
Code:
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
    objWorkbookOutput.Close
End Sub
 
Last edited:
Top