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:
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.
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
ActiveWorkbook.Sheets(mysheet).Activate
mygotvalue = Range(mycell).Value
ActiveWorkbook.Close False
Windows(“ZTL-Infosheet.xlsm”).Activate
ActiveCell.Offset(x, 0).Value = mygotvalue
Next x
End Sub
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!!
give me your phone no: / mail / address
how to excel file import or export without opening . not allowed read only .