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

Published by

Dinesh Kumar Takyar

Welcome to! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: For a structured Excel VBA training course online you can visit:

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

  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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.