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:

4 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

Leave a Reply

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