I need help with my code below.
I am copying from one workbook to another and pasting it in the first open cell (This part is working fine)
The Vlookups are also pasting in the right spot, but if the Vlookup is in Cell G45, I need the Vlookup to look at A45, currently it is hard coded at A2. How can I make that variable?
Second issue: I need to auto fill the vlookup cells to the bottom - Assuming this also needs to be variable.
Thank you for your input.
Private Sub Vlookup()
Dim RLReport As Variant
Dim LastRow As Integer
Dim EndLastRow As Integer
Workbooks("Destination").Worksheets("Data").Activate
LastRow = Range("A1").End(xlDown).Row
Workbooks.Open Filename:="Source A"
Windows("Source B").Activate
RLReport = Range("A15", Range("A15").End(xlDown).End(xlToRight)).Value
Workbooks("Destination").Worksheets("Data").Range("A" & LastRow + 1).Resize(UBound(RLReport, 1), UBound(RLReport, 2)).Value = RLReport
Workbooks("Destination").Worksheets("Data").Activate
Range("G" & LastRow + 1) = "TV Mounts"
Range("H" & LastRow + 1) = "=IFERROR(VLOOKUP(A2,'[Category Mod Changes - D72 - TV Wall Mounts.xlsx]TV Mounts'!$A:$J,8,FALSE),""Not Traited"")"
Range("I" & LastRow + 1) = "=IFERROR(VLOOKUP(A2,'[Category Mod Changes - D72 - TV Wall Mounts.xlsx]TV Mounts'!$A:$J,9,FALSE),""Not Traited"")"
Range("J" & LastRow + 1) = "=IFERROR(VLOOKUP(A2,'[Category Mod Changes - D72 - TV Wall Mounts.xlsx]TV Mounts'!$A:$J,10,FALSE),""Not Traited"")"
EndLastRow = Range("A1").End(xlDown).Row
' Range("g2:j2").AutoFill Destination:=Range("g2:j" & EndLastRow)
I am copying from one workbook to another and pasting it in the first open cell (This part is working fine)
The Vlookups are also pasting in the right spot, but if the Vlookup is in Cell G45, I need the Vlookup to look at A45, currently it is hard coded at A2. How can I make that variable?
Second issue: I need to auto fill the vlookup cells to the bottom - Assuming this also needs to be variable.
Thank you for your input.
Private Sub Vlookup()
Dim RLReport As Variant
Dim LastRow As Integer
Dim EndLastRow As Integer
Workbooks("Destination").Worksheets("Data").Activate
LastRow = Range("A1").End(xlDown).Row
Workbooks.Open Filename:="Source A"
Windows("Source B").Activate
RLReport = Range("A15", Range("A15").End(xlDown).End(xlToRight)).Value
Workbooks("Destination").Worksheets("Data").Range("A" & LastRow + 1).Resize(UBound(RLReport, 1), UBound(RLReport, 2)).Value = RLReport
Workbooks("Destination").Worksheets("Data").Activate
Range("G" & LastRow + 1) = "TV Mounts"
Range("H" & LastRow + 1) = "=IFERROR(VLOOKUP(A2,'[Category Mod Changes - D72 - TV Wall Mounts.xlsx]TV Mounts'!$A:$J,8,FALSE),""Not Traited"")"
Range("I" & LastRow + 1) = "=IFERROR(VLOOKUP(A2,'[Category Mod Changes - D72 - TV Wall Mounts.xlsx]TV Mounts'!$A:$J,9,FALSE),""Not Traited"")"
Range("J" & LastRow + 1) = "=IFERROR(VLOOKUP(A2,'[Category Mod Changes - D72 - TV Wall Mounts.xlsx]TV Mounts'!$A:$J,10,FALSE),""Not Traited"")"
EndLastRow = Range("A1").End(xlDown).Row
' Range("g2:j2").AutoFill Destination:=Range("g2:j" & EndLastRow)