Solved: Assign a variable to sheet name

[fredlo2010]

Hello guys,

OK i am trying to assign a variable to a sheet name so it can be used in a formula as reference.

I have a workbook with three worksheets:

1. A reference worksheet that's hidden and its only there with notes and formula in case I have to the operation manually.

2. A sheet with more references and named ranges I use in calculations. Also hidden

3. A worksheet with data that changes all the time.

What the macro does is. It will create a new sheet and its going to add formulas to this new sheet to create a report. The problem is that some of the formulas will refer on the original data worksheet.

Is there a way to assign that worksheet name a string variable that I can later on use in my formulas?

set the name to the worksheet that's is not hidden and its not the current active worksheet or something like that. Maybe there is a better way to do this.

Thanks a lot for the help

[CatDaddy]

str = Worksheets(1).Name

[fredlo2010]

Thanks CatDaddy,

I knew i was forgetting something, the index is different not always the same. Or the order.

Sorry

[CatDaddy]

is the sheet always the same name? you could just assign that name to a string

[fredlo2010]

No the name will change too.. it sort of works with the idea you gave me. But I am afraid I will encounter issues in the future. I made sure that Sheet with data is Sheet(1) and then the new report sheet I create is before that one to get Sheet(1) for the report.

[CatDaddy]

so is the sheet you are trying to reference always the last sheet?

if so you could use
str = Sheets(Sheets.count).Name

[Paul_Hossler]

Could you use the Code Name (I think that's what's called)?

In the Project, select the WS you want as a Data sheet and in Properties, give it a name.

Whatever that sheet gets called in the tab the VBA code name remains, and .Name will retreive it

So DataSheet. Cells .... is the same as Worksheets ("DataWorksheet").Cells ....

Paul

[mikerickson]

Its unlikely that a worksheet formula will refer to only a sheet.

If you set the External argument of .Address, you can use range addresses

Dim aRange as Range

Set aRange = Sheet1.Range("A1:A10"):Rem note use of code name

Sheets("NewSheet").Range("B2").Formula = "=SUM(" & aRange.Address(,,,True) & ")"

[fredlo2010]

Thanks for the help guys I am using the Catdaddy suggestion

str = Worksheets(1).Name

I think is the best option here.

Thanks a lot for all the tips and support.
:)

[fredlo2010]

Thanks for the help mikerickson,

I will have to research a little about this because this is the first time I see .address in action.

Do you know a good article I can use as reference. Or in books under what chapter is most likely for me to find it.

Thanks

[mikerickson]

No, I don't know an article, but a little expermentation will show the different ways that the arguments can alter the string that results.
They are fairly intuitive, except for RelativeTo. Note that in the below, both A1 and A2 hold the formula =A1 (relative references)

Dim a1Str As String, a2Str As String

a1Str = Range("B2").Address(False, False, xlR1C1, False, Range("A1"))
a2Str = Range("B2").Address(False, False, xlR1C1, False, Range("A2"))

MsgBox a1Str & vbCr & a2Str

Range("a1").FormulaR1C1 = "=" & a1Str
Range("A2").FormulaR1C1 = "=" & a2Str

The string result of .Address can be used to build formulas, as above.