Sheet Numbers Q

  • Thread starter Thread starter Sean
  • Start date Start date
S

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
 
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
 
But the sheet I want to
reference will always be the "2nd" sheet from left in my workbook
 
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
Click to expand...

That did the trick, Thanks
 
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
Click to expand...

That did the trick, Thanks
 
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
 
hi,
you're right. but he used sheet1 in his formula. i assumed his sheet name
were numbered.

Regards
FSt1
 
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
 
That did the trick, Thanks
Click to expand...

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
 
Back
Top