Accessing Other Sheet Tabs from within Excel

[Eric]

Is there any easy way to refer to sheets without knowing the name of the sheet?

e.g. A summary sheet that collates details for the company from all other
sheets within the workbook which may be different branches and/or personnel.

These extra tabs may be added or have their tab name changed at any time,
and I do not wish to redo the summary sheet each time.
Ideally I would like to know how many other sheets there are are and to be
able to reference instead of 'Freds Sheet'!.$A$7 to be something like
WorkSheet[3]!.$A$7, with some method of checking the range of sheets - the
summary could always be the first sheet.

The sheets, other than the summary, would all be in the same format, so that
the data required would be accessed from 'fixed' cells.

 

[p45cal]

Sub blah()
NumberOfSheetsToProcess = ThisWorkbook.Sheets.Count - 3 '3 from below
MsgBox NumberOfSheetsToProcess & " sheets to summarise."
For Each sht In ThisWorkbook.Sheets
Select Case sht.Name
Case "Sheet name not to be processed", "Summary", "leaveMeAlone"
3 here
'do nothing
Case Else
MsgBox "about to process sheet '" & sht.Name & "'"
'do lots of summarising using sht as your way of referencing tha
sheet
End Select
Next sht
End Su

 

[Eric]

Thanks for your info, I haven't had chance to try it yet, but I was hoping to
be able to code a cell with something of the order:-
=if(Row()>Worksheets.Count,"",Worksheet[Row()]!$A$7)
instead of
='Freds Sheet'!$A$7

Is this a possibility?

 

[p45cal]

Yes, I realised just after posting that you'd asked this in th
-Worksheet Functions- section of codecage.
In answer to your question, I very much doubt it.
However, although it's not something I've done myself yet, do a searc
for
"*Consolidation*" or "*Consolidate data in multiple worksheets*" i
Excel Help. It seems there is built-in functionality that might be abl
to help you

 

[Tom Hutchins]

If I understand correctly, you want a function you can enter in, say, A2 on
your summary sheet and copy down. On row 2, it would pull the value in A7
from the second sheet in the workbook. On row 3, it would pull the value in
A7 fromthe third sheet, etc.

If you are always pulling numeric values from the other sheets, you can use
this used-defined function (UDF):

Public Function GetShtVal(CurrRow As Long, GetRng As String) As Variant
On Error GoTo GSVerr
If CurrRow > ThisWorkbook.Sheets.Count Then
GetShtVal = vbNullString
Else
GetShtVal = ThisWorkbook.Sheets(CurrRow).Range(GetRng).Value
End If
Exit Function
GSVerr:
GetShtVal = Error()
End Function

In A2 (for example) on your summary sheet, you would enter
=GetShtVal(ROW(),"A7")+(NOW()*0)

The Now()*0 is necessary to force Excel to recalculate the formula (the UDF
won't automatically recalc).

If you are always pulling text values, you can use the same UDF, but the
fomula on the worksheet will have an ampersand instead of the plus sign:
=GetShtVal(ROW(),"A7")&TEXT(NOW()*0,"")

If you have a mix of values & text, we can handle that with a second UDF:

Public Function TxtOrVal(InTxt As String) As Variant
If Len(InTxt) = 0 Then
TxtOrVal = vbNullString
Exit Function
End If
If Len(Str(Val(InTxt))) < Len(InTxt) Then
TxtOrVal = InTxt$
Exit Function
End If
TxtOrVal = Val(InTxt)
End Function

In A2 (for example) on your summary sheet, you would enter
=TxtOrVal(GetShtVal(ROW(),"A7")&TEXT(NOW()*0,""))

The user-defined functions need to be in a general VBA module in the
workbook where you want this to work. If you are new to user-defined
functions (macros), this link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Hope this helps,

Hutch

Thanks for your info, I haven't had chance to try it yet, but I was hoping to
be able to code a cell with something of the order:-
=if(Row()>Worksheets.Count,"",Worksheet[Row()]!$A$7)
instead of
='Freds Sheet'!$A$7

Is this a possibility?