July 1, 2016

How to copy column data from closed workbook with VBA

A user wants to copy columns of data from one workbook into another active workbook without opening the workbook. First we create an instance of the Excel application, open the workbook using the path of the workbook, copying the relevant data, closing this instance and finally pasting the copied data into our active workbook. Watch the training video and then study the VBA code:

Watch this video on YouTube.

The complete VBA code:

Sub CopyDataFromClosedWbk()
‘copy data from closed workbook to active workbook
Dim xlApp As Application
Dim xlBook As Workbook
Dim Sh As Object
Set xlApp = CreateObject(“Excel.Application”)
‘Path source workbook
Set xlBook = xlApp.Workbooks.Open(“C:\Users\takyar\Desktop\test-workbook.xlsx”)
xlApp.DisplayAlerts = False
Set xlBook = Nothing
Set xlApp = Nothing
Set xlBook = ActiveWorkbook
Set Sh = xlBook.Sheets(“Sheet1”)

End Sub

9 thoughts on “How to copy column data from closed workbook with VBA

  1. how to copy a range by selection of mouse and paste it in an area of another workbook/worksheet with formats and formulas ?(i have a short macro which copies a selected range but only send it to the printer!)
    this module to be active in all the workbooks available.
    thank you for the great service! i am learning a lot!

  2. Hi Sir,

    i am using Create Summary Sheet from Multiple workbook it is working fine for .xlsx files but when we apply it for .xlsm it is showing runtime error ‘9’ script out of range. and when i pressed f8 then it is automatically went to another macro which are already built in template from which i want ot copy data to my Summary Sheet . So Please suggest me how to handle runtime error 9 .
    Or you can also guide me how to transfer data from opened excel file to fixed excel file .

    Thanks & Regards
    Rohit Kumar

    1. sent me your file which you met error .i will confirm it in details _or call me my Skype is tonguyen

  3. ‘Path source workbook
    Set xlBook = xlApp.Workbooks.Open(“C:\Users\Keisha Louisse\Desktop\test.xlsx”)

    Compiler error. list separator or )

  4. Nice coding, thanks for that! How can I change this line:

    Set xlBook = xlApp.Workbooks.Open(“C:\Users\takyar\Desktop\test-workbook.xlsx”)

    To a “Select File Dialoge” as the above line is too hard-coded? I want to select the “test-workbook” by a Dialoge.


  5. Hi Dinesh,

    Good post and I learned much.
    How can i copy the sheet from Closed workbook and moved to Active workbook using VBA. Could you please help me on this.


  6. Hi I need Copy all the data in the closed workbook. What is the code can I used instead of

    I used “lastrow” method like,
    xlBook.Sheets(“Sheet1”).Range(Cells(1, 1), Cells(lastrow, 1)).Copy

    However I got the error like “Application-defined or object-defined error”

    Please help me on this as it is useful for me in office. Thanks in advance.

  7. Hi, how do I pastespecial with this code, it works perfectly for me but I cannot seem to paste value and number formats. Would really appreciate your help

Comments are closed.