I substituted the 'name' with the " & Page8.Name & _" and it wouldn't run at
all?


I have now got around problem by changing sheet name to BB SCM and then
adding date to name at the end of the macro


thanks for your assistance

James

"JE McGimpsey" wrote:

OK - I get it now - you're not asking about the *formula* changing when
the sheet name changes (which it does), you're asking how to reference
the sheet name within the macro.

Just don't hard-code the sheet name:

Range("A1").FormulaR1C1 = "=INDEX('" & Page8.Name & _
"'!C, (ROW()-1)*8+2)"

Note that you almost *never* need to select anything. Working with Range
objects directly makes your code faster, usually smaller, and IMO easier
to maintain.

In article ,
JBW wrote:

I knew I'd tried this, as soon as you change the sheet name the system opens
an explorer window and asks you to locate the new file.

code is:

Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('BB SCM 22 OCTOBER 2007'!c, (ROW()-1)*8+2)"
date is only thing that changes.

So....


Is there a sheet reference I can use in place of the name to ensure the
correct sheet is always used?