VLOOKUP To Previous Sheet

[BrittKnee]

Hi All. I am trying to code a dynamic VLOOKUP that references the sheet before my current sheet. The reason is that I am copying a new sheet with updated data to the workbook and then need to compare the old data (previous sheet) to the new in order to flag newly added records. I am currently using the below formula but am getting a #NAME? error. I'm assuming it has to do with the previous sheet reference. Any and all help is appreciated.

Range("T2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP([@[Position '#]],'" & ActiveSheet.Previous.Name & "'!D:S ,16,0)"

 

[Joe4]

Try replacing this:

ActiveSheet.Previous.Name

with this:

Sheets(ActiveSheet.Index - 1).Name

 

[BrittKnee]

That worked except it added () around Column S in the range

=VLOOKUP([@[Position '#]],'Raw data_0912'!D:(S),16,0)

 

[Joe4]

You are are entering the formula in normal referencing, and not R1C1 referencing, do not use "FormulaR1C1".
You can also combine your code into one line (no need to select a range before working with it), i.e.

Range("T2").Formula = "=VLOOKUP([@[Position '#]],'" & Sheets(ActiveSheet.Index - 1).Name & "'!D:S ,16,0)"

 

[BrittKnee]

That worked! Thanks!

 

[Joe4]

You are welcome.
Glad I was able to help!