List of worksheets

  • Thread starter Thread starter wally
  • Start date Start date
W

wally

In a workbook with multiple worksheets, each with a unique name, how would I
create a list of of the worksheets? In addition, I'd like to be able to show
totals from each sheet beside the sheet's name.
 
wally said:
In a workbook with multiple worksheets, each with a unique name, how would I
create a list of of the worksheets? In addition, I'd like to be able to show
totals from each sheet beside the sheet's name.
Click to expand...

In a new sheet,
run the sub below to list all sheetnames in A2 down

Sub ListSheetNames()
Dim wkSht As Worksheet
Range("A2").Select
For Each wkSht In Worksheets
Selection = wkSht.Name
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
Next wkSht
End Sub

(Clear A2 which will contain the sheetname of the new sheet)

Then enter the key* cell refs in say, B1 across, eg: E5, E20, K5, K20 etc
*It's assumed that all sheets are identically structured, and that you want
to retrieve the contents of these key cells from each sheet which contain the
"totals", or what-have-you

Place in B3: =INDIRECT("'"&$A3&"'!"&B$1)
Copy B3 across/fill down to populate
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
 
Probably doesn't matter in this case Max, but selecting and activating slows
thing down a bit.

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
End With
Next i
End Sub


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


Gord
 
Back
Top