Get Column Data from Closed Workbook

How to get column data from a closed workbook into another workbook automatically using VBA. Here’s a picture of the workbook with data:

Workbook with data
Workbook with data

Here’s an image of the workbook which will get the data from the above workbook:

Receiving workbook with the macro
Receiving workbook with the macro

Watch the video before studying the complete VBA code to transfer column data from one workbook to another:


You can watch this video on YouTube.

Sub copyColData()
Dim lastRow As Long
Dim myApp As Excel.Application
Dim wkBk As Workbook
Dim wkSht As Object

Set myApp = CreateObject(“Excel.Application”)
Set wkBk = myApp.Workbooks.Open(“C:\Users\takyar\Desktop\mydata.xlsx”)

lastRow = wkBk.Sheets(1).Range(“D” & Rows.Count).End(xlUp).Row
wkBk.Sheets(1).Range(“D1:D” & lastRow).Copy
myApp.DisplayAlerts = False
Set wkBk = Nothing
Set myApp = Nothing
Set wkBk = ActiveWorkbook
Set wkSht = wkBk.Sheets(“Sheet1”)
Exit Sub

End Sub

Further reading:

Get data from closed workbook

Looping through Directory

Loop through sub-folders

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:

One thought on “Get Column Data from Closed Workbook”

  1. Hey

    I’m a student from Israel and I love your channel !! It helps me a lot!

    I have a question for you

    Can you please show me how to get value from a closed work book using VBA and the user form.
    I have to put the employee number on the form, and then, his name should appear in the following text box as I update it.
    the names and Employee numbers are placed in another closed workbook file in a different track.

Leave a Reply

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