mapaulie
02-17-2021, 11:20 AM
Hi,
I'm a Newbie to Excel VBA.
I have 2 .xlsx files named, Template.xlsx and RequestChange.xlsx (the data in this file is in a Table view). Trying to Vlookup data and have it returned in the Template file, while RequestChange.xlsx is a closed book.
I wrote the following code but keeps getting stuck on referencing to the Table. Also, end goal is to Vlookup the whole Table and return data in specific columns, like column B, C, E, F (for example).
Please help! Tia
---------------------------------------------------------------------------------------------
Sub Vlookup()
Dim rw As Long, x As Long, x As Range
Dim extwbk As Workbook, twb As Workbook
Set twb = ThisWorkbook
Set extwbk = Workbooks.Open("C:\Users\xxxx\MyDocument\RequestChange.xlsx", True, True)
Set x = extwbk.Worksheets("Sheet1").ListObjects("Change__2").Range.Select
With twb.Sheets("Form")
For rw = 2 To .Cells(Rows.Count, 1).End(x1Up).Row
On Error Resume Next
.Cells(rw, 2) = Application.Vlookup(.Cells(rw, 1).Value2, x, 2, False)
Next rw
End With
extwbk.Close savechanges:=False
End Sub
I'm a Newbie to Excel VBA.
I have 2 .xlsx files named, Template.xlsx and RequestChange.xlsx (the data in this file is in a Table view). Trying to Vlookup data and have it returned in the Template file, while RequestChange.xlsx is a closed book.
I wrote the following code but keeps getting stuck on referencing to the Table. Also, end goal is to Vlookup the whole Table and return data in specific columns, like column B, C, E, F (for example).
Please help! Tia
---------------------------------------------------------------------------------------------
Sub Vlookup()
Dim rw As Long, x As Long, x As Range
Dim extwbk As Workbook, twb As Workbook
Set twb = ThisWorkbook
Set extwbk = Workbooks.Open("C:\Users\xxxx\MyDocument\RequestChange.xlsx", True, True)
Set x = extwbk.Worksheets("Sheet1").ListObjects("Change__2").Range.Select
With twb.Sheets("Form")
For rw = 2 To .Cells(Rows.Count, 1).End(x1Up).Row
On Error Resume Next
.Cells(rw, 2) = Application.Vlookup(.Cells(rw, 1).Value2, x, 2, False)
Next rw
End With
extwbk.Close savechanges:=False
End Sub