Macro Insert Vlookup Formula

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.

Well I think I have it figured out. I need I2:J25 to be constant, E8 will actually depend on the row as the formula is copied down (E8, E9, E10, etc). and the Indirect Portion (B8) will be similar to E8.

The code I am using is:

With Range("F8:F1000")
.Formula = "=VLOOKUP(E8,INDIRECT(""'""&B8&""'!I2:J25""),2,FALSE)"
End With