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: