is it possible

[MAMIBUSSOL]

I have a variable called SALES1

I have a worksheet called SALES1 (this name could change)

normal coding would give me

SALES1 = "=SUM('SALES1'!B37)

the problem is how do I write coding which will cater for the worksheet changing name.

 

[Biz]

Hi,

Not sure if this helps.

Sub Test()
Range("B38").Formula = "=SUM(" & ActiveSheet.Name & "!B37)"
End Sub

Biz

 

[MAMIBUSSOL]

thanks for respoonding Biz, it does help with the a possible solution, however I would still have the problem of not knowing the name of the sheet. Unless I misunderstood the code you gave me; I would still need to select the worksheet; and this could change. But you have helped with a the next phase which would have been how to do the calculation.

 

[JugglerJAF]

Why not rename your variable (or the sheet name) so that they do not share the same name.

You could use any of the following and still have a variable and sheet "called" SALES1:
SALES1
SALES 1
SALES_1
SALES(1)
SALES01
var_SALES01

 

[MAMIBUSSOL]

my master file is made up of the following worksheets
VAT
SALES1 - this will get changed to a month and year ie APR11
SALES2 - ETC
SALES3 - ETC

the master file will get used to record the data, sales1 2 and 3 will get changed to a monthyear value depending on when its used.

my coding needs to be flexible to deal with this occurance

for example first time the file is used SALES1 might be called APR11
however next time I use the file SALES1 might be called JUL11, as it isn't practical to adjust the coding each time, It makes logical sense to make the coding flexible enough to cater for this problem

 

[mikerickson]

There are two ways to do this.
One would be with Named Ranges.
If you have Name: myRange RefersTo:Sales1!$A$1, then the RefersTo will adjust as the user changes the name of the sheet (or the tab name changes via VBA).

The other way involves the use of code names.
In VBA you can refer to a sheet by its tab name
Sheets("Sales1").Range("A1")
or by its code name
Sheet1.Range("A1")

(more info at this link http://www.mrexcel.com/forum/showthread.php?t=253546&highlight=codename)

To write formulas

ActiveSheet.Range("A1") = "=SUM(" & Sheet1.Range("B37").Address(True,True,xlA1,True) & ")"

 

[John Davis]

I have a variable called SALES1

I have a worksheet called SALES1 (this name could change)

normal coding would give me

SALES1 = "=SUM('SALES1'!B37)

the problem is how do I write coding which will cater for the worksheet changing name.

Does this help any?

http://www.cpearson.com/excel/sheetref.htm