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

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

  1. raja

    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!


    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 *