Sheet Numbers Q

[Sean]

Is it possible to reference a cell by its worksheet number, rather
than its sheet name?

For e.g. Sheet1 A1 as "=worksheet(1)a1

I've tried above and it doesn't work. Reason I ask is that I have
sheets that are added and change each week. But the sheet I want to
reference will always be the "2nd" sheet from left in my workbook

Thanks

 

[FSt1]

hi
you were so close.
try this....
=sheet1!A1

regards
FSt1

 

[Don Guillett]

Put this in a REGULAR module and then use as a formula
=sht2("a1")

Function sht2(x)
Application.Volatile
sht2 = Sheets(2).Range(x)
End Function

 

[Don Guillett]

But the sheet I want to
reference will always be the "2nd" sheet from left in my workbook

 

[David Biddulph]

But surely that won't work if the sheet name is changed?

 

[Sean]

Put this in a REGULAR module and then use as a formula
=sht2("a1")

Function sht2(x)
Application.Volatile
sht2 = Sheets(2).Range(x)
End Function

That did the trick, Thanks

 

[Don Guillett]

Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Put this in a REGULAR module and then use as a formula
=sht2("a1")

Function sht2(x)
Application.Volatile
sht2 = Sheets(2).Range(x)
End Function

That did the trick, Thanks

 

[FSt1]

hi
you're right. but all my formula is is a re-write of the formula he tried,
ment only as an example. he could re-write to fit.

regards
FSt1

 

[FSt1]

hi,
you're right. but he used sheet1 in his formula. i assumed his sheet name
were numbered.

Regards
FSt1

 

[Gord Dibben]

Only if using VBA.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

FSt1

OP wants to use the codename of the sheet, not the name of the sheet.

Run this to see the difference.

Sub CreateListOfSheetsOnFirstSheet()
Dim ws As Worksheet
For i = 1 To Worksheets.Count
With Worksheets(1)
Set ws = Worksheets(i)
.Cells(i, 1).Value = ws.Name
.Cells(i, 2).Value = ws.CodeName
End With
Next i
End Sub


Gord Dibben MS Excel MVP

 

[Sean]

That did the trick, Thanks

It seems under certain conditions, the above returns #Value! and when
I edit the cell and Return, the correct value appears again. Not
really what the circumstances are. Why is this?

It maybe because I extract two sheets ("Summary Report" within which
my formula =sht2("a1") appears) and the 3rd sheet. Thus I have only
effectively 2 sheets now in the workbook where my formula runs. See
code below for sheet extraction. It is only after these I change all
cells to values. Is there any way around this?

Set Sourcewb = ActiveWorkbook

i = Worksheets("Summary Report").Index
wsVar = Worksheets(i + 2).Name
Sourcewb.Sheets(Array("Summary Report", wsVar)).Copy
Set Destwb = ActiveWorkbook

For Each sh In Destwb.Worksheets
sh.Select
With sh.UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False
Destwb.Worksheets(1).Select
Next sh