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