msquared99
02-03-2017, 06:59 AM
I need some help. I have a macro where I am doing a VLOOKUP from one sheet to another. The issue is that the workbook that the macro opens will have a different name and the worksheet within that workbook will have a different name.
Here is what the VLOOKUP looks like in the spreadsheet: =VLOOKUP(RC[-1],'[SalesLimit1-01262017-AM.xls]SalesData-012620'!R2C14:R7C14,1,0)
Here is what I need it to look like in the macro code: "=VLOOKUP(RC[-1],'"
& fName1 & WBName & "'!R2C14:R7C14,1,0)
So, fName1 replaces SalesLimit1-01262017-AM.xls (the variable workbook name) and WBName replaces SalesData-012620 (the variable
tab name)
Cross posted here: http://chandoo.org/forum/threads/vlookup-in-macro.32913/
Sub GetFile()
Dim fName1 As Variant, wbo As Workbook
Dim fName2 As Variant, wba As Workbook
Dim WBName As Worksheet
fName1 = Application.GetOpenFilename(filefilter:="Excel Files (*.XLS), *.XLS", Title:="Select the prior spreadsheet:")
If fName1 = False Then Exit Sub
Set wbo = Workbooks.Open(fName1)
wbo.Activate 'New code
Set WBName = wbo.Worksheets(1) 'New code
fName2 = Application.GetOpenFilename(filefilter:="Excel Files (*.XLS), *.XLS", Title:="Select the current spreadsheet:")
If fName2 = False Then Exit Sub
Set wba = Workbooks.Open(fName2)
wba.Activate
Range("M2").Value = "1"
Range("M2").Select
Selection.AutoFill Destination:=Range("m2:m" & Cells(Rows.Count, "A").End(xlUp).Row)
Range("N2").FormulaR1C1 = "=RC[-13]&RC[-10]"
Range("N2").Select
Selection.AutoFill Destination:=Range("N2:N" & Cells(Rows.Count, "A").End(xlUp).Row)
Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-1],'" & fName1 & WBName & "'!R2C14:R7C14,1,0)"
'Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-1],'[SalesLimit1-01262017-AM.xls]SalesData-012620'!R2C14:R7C14,1,0)"
Range("O2").Select
Selection.AutoFill Destination:=Range("O2:O" & Cells(Rows.Count, "A").End(xlUp).Row)
wbo.Close savechanges:=True
wba.Close savechanges:=True
End Sub
Here is what the VLOOKUP looks like in the spreadsheet: =VLOOKUP(RC[-1],'[SalesLimit1-01262017-AM.xls]SalesData-012620'!R2C14:R7C14,1,0)
Here is what I need it to look like in the macro code: "=VLOOKUP(RC[-1],'"
& fName1 & WBName & "'!R2C14:R7C14,1,0)
So, fName1 replaces SalesLimit1-01262017-AM.xls (the variable workbook name) and WBName replaces SalesData-012620 (the variable
tab name)
Cross posted here: http://chandoo.org/forum/threads/vlookup-in-macro.32913/
Sub GetFile()
Dim fName1 As Variant, wbo As Workbook
Dim fName2 As Variant, wba As Workbook
Dim WBName As Worksheet
fName1 = Application.GetOpenFilename(filefilter:="Excel Files (*.XLS), *.XLS", Title:="Select the prior spreadsheet:")
If fName1 = False Then Exit Sub
Set wbo = Workbooks.Open(fName1)
wbo.Activate 'New code
Set WBName = wbo.Worksheets(1) 'New code
fName2 = Application.GetOpenFilename(filefilter:="Excel Files (*.XLS), *.XLS", Title:="Select the current spreadsheet:")
If fName2 = False Then Exit Sub
Set wba = Workbooks.Open(fName2)
wba.Activate
Range("M2").Value = "1"
Range("M2").Select
Selection.AutoFill Destination:=Range("m2:m" & Cells(Rows.Count, "A").End(xlUp).Row)
Range("N2").FormulaR1C1 = "=RC[-13]&RC[-10]"
Range("N2").Select
Selection.AutoFill Destination:=Range("N2:N" & Cells(Rows.Count, "A").End(xlUp).Row)
Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-1],'" & fName1 & WBName & "'!R2C14:R7C14,1,0)"
'Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-1],'[SalesLimit1-01262017-AM.xls]SalesData-012620'!R2C14:R7C14,1,0)"
Range("O2").Select
Selection.AutoFill Destination:=Range("O2:O" & Cells(Rows.Count, "A").End(xlUp).Row)
wbo.Close savechanges:=True
wba.Close savechanges:=True
End Sub