vba formula problem

[BobtBuilder]

I have been trying to figure out how to go about this.
I have a formula
=TAKE(FILTER(INDIRECT("'" & I11 & "'!K2:K" & (MATCH(9.99999999999999E+307, INDIRECT("'" & I11 & "'!K:K")) + 1)), (INDIRECT("'" & I11 & "'!A2:A" & (MATCH(9.99999999999999E+307, INDIRECT("'" & I11 & "'!A:A")) + 1)) <= TODAY()) * (INDIRECT("'" & I11 & "'!A2:A" & (MATCH(9.99999999999999E+307, INDIRECT("'" & I11 & "'!A:A")) + 1)) <> "")),-1)
that i need to copy to a new cell in my vba code, but i need to change I11 to the new cell, always will be "I" but the row will change. Have tried "I" & row(), but it does not work, gives me a name error
Any ideas?

 

[Joe4]

What cell is the original formula with the "I11" reference found in?
For the rows you are copying it to, what is the relationship between what row the "I11" reference should change to, and what cell the formula is being copied to?

 

[RoryA]

Typically just assigning the FormulaR1C1 property from one cell to the other will work, if you want the same effect as manually copying it.

 

[BobtBuilder]

Joe,
I11 is the cell and the cell being copied to would be I plus the row number ie I12
Basically I am populating a table with worksheet names via a form, and need to be able to later access the worksheet by that table.
So I need to make I11 and , say, I12 variables depending on where they are being copied from

 

[BobtBuilder]

Rory,

Tried that but with variables to determine the I11 or I12 reference i can not seem to figure out the syntax. Ie I11= "I" & prev. which is the last cell used or nextlocation which is the next cell being used