I am trying to create a macro to insert a vlookup formula into a column of a report. There are two things I am unsure of though. 1. I would like formula to look in the sheet based off a value in column A. 2. I am not sure how to have some cells relative and some absolute within the macro.

In my example:
A1 = 123456
A2 = 987654

F1 = vlookup(E1,'123456'!,$I$1:$J$1000,2,False)
F2 = vlookup(E1,'987654'!,$I$1:$J$1000,2,False)

Note that the formula looks up the Sheet (123456 or 987654) based on the value in column A.

Thanks for your help.