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:

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

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
wkBk.Close
myApp.Quit
Set wkBk = Nothing
Set myApp = Nothing
Set wkBk = ActiveWorkbook
Set wkSht = wkBk.Sheets(“Sheet1”)
wkSht.Activate
Range(“A1”).Select
wkSht.Paste
Exit Sub

End Sub

Further reading:

Get data from closed workbook

Looping through Directory

Loop through sub-folders