Sheet name needed for formula

[Guest]

in VBA sheet is sheet8

how can this appear in =index('sheetname'! c,(row()-1)*8+2)


macro is currently selecting sheets name as appears on the tab but this
changes every day. I need to use vba identity within this formula for it to
work.


James

 

[JE McGimpsey]

If 'sheetname' is renamed, XL will automatically change the reference in
the formula. In the stored formula, XL keeps track of the Code Name
(e.g., Sheet8), but displays the sheet's Name (e.g., 'sheetname') in the
formula bar.

 

[Guest]

So your saying that although my macro names the sheet with a date, tomorrow
when it changes the name (as the date is different) the formula will
automaticlly change the name too as it uses the reference ?

if so hurrah

is ther still not a way of saying sheet8 within the formula though?

 

[JE McGimpsey]

If you don't want to take my word for it, you could actually *try* it...



BTW - you *ARE* "saying sheet8" within the formula. Formulas aren't
stored as the text you enter, they're tokenized. When you enter

=mysheetname!A1

XL tokenizes "mysheetname" to refer to the sheet's code name (Sheet8).

Then when you select the cell, whatever the value of Sheet8's Name
property is will be used in the sheet reference when expanding the
tokenized formula to the formula bar or cell.

 

[Guest]

I'll try it tomorrow when the date and hence the name chamges, I'm sure I
tried it before and excel went looking for the name as it had changed

 

[Peo Sjoblom]

You know you don't have to wait for tomorrow if you are using the computers
internal clock, just change the date to tomorrow and open up a copy of this
file to test it. Then change back the date


--


Regards,


Peo Sjoblom

 

[JE McGimpsey]

Or you could take 30 seconds to open a new workbook, enter

=Sheet2!A1

in Sheet1, cell A1. Then, in the VBE's immediate window, enter

Sheet2.Name = "newname"

Exit the VBE and verify that Sheet1!A1 now reads

=newname!A1

 

[Guest]

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?

James

 

[JE McGimpsey]

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.

 

[Guest]

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]

" _" (space-underscore) is a line continuation marker in the VBE. It
indicates that what follows on the next line is a continuation of the
current line. See "Writing Visual Basic Statements" in XL Help.

You don't say what "wouldn't run at all" means (did you get a compile
error?, a run-time error? a crash?), but I assume you substituted Page8
with your worksheet's Code Name.