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:

5 thoughts 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.

  2. Hello, my task requires me to use variables for my SheetName, because the data I want to get from another closed workbook is published weekly on a new Sheet, I’m required to get the last published sheet…. I have written the following code but still not working, kindly help me or suggest another way of doing this, mind you the close workbook is saved in a central databank, all changes take place centrally and must be updated on my worksheet, when any changes been made.

    Sub DataQuoteAufrufen()
    Range(“I57”).Select ‘activecell
    totalrows = ActiveCell.CurrentRegion.Rows.Count – 1

    For x = 1 To totalrows
    mypath = ActiveCell.Offset(x, -4).Value
    mysheet = ActiveCell.Offset(x, -3).Value
    mycell = ActiveCell.Offset(x, -2).Value

    Workbooks.Open Filename:=mypath
    mygotvalue = Range(mycell).Value

    ActiveWorkbook.Close False
    ActiveCell.Offset(x, 0).Value = mygotvalue

    Next x

    End Sub

  3. Hello,
    I’m french student and I work on VBA project, your channel is very good to learn.

    I have a question, when I use this code, I have an error message
    “The license information for this component can not be found. You do not have the appropriate license to use this feature in the authoring environment.”
    Have you a reply for this probleme?

    Thank you in advance!!

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.