Get sheet names

[JHL]

Hello
I have a spreadsheet of 18 sheets each with a different name. I want to
pull those sheetnames into one more sheet in column A. From there I have a
formula to summarize data from those sheets, but it's a bear to have to type
each sheetname first.

Sheetnames
North
South
East
West
etc..

Thanks in advance.

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in and run it. You will
get a liest of sheet names in column a starting in a2

Sub sonic()
For x = 1 To Worksheets.Count
Cells(x + 1, 1) = Sheets(x).Name
Next
End Sub

Mike

 

[JHL]

Mike H

PERFECT! Thanks.

Hi,

Right click your sheet tab, view code and paste this in and run it. You will
get a liest of sheet names in column a starting in a2

Sub sonic()
For x = 1 To Worksheets.Count
Cells(x + 1, 1) = Sheets(x).Name
Next
End Sub

Mike

 

[srctr]

I like this, but is there a way to reverse the order. Instead of the sheets
being ordered 1st thru last sheet, put it last sheet name in A2 and the first
sheet in the last cell?

 

[Dave Peterson]

For x = 1 To Worksheets.Count
becomes
For x = Worksheets.Count to 1 step -1

 

[srctr]

It didn't do anything different. It gives me the sheets in the same order as
the other. Say my sheets are March, February, January. Instead of it
showing as March in A2, February in A3, January in A4. I would like it to
show me January in A2, February in A3, March in A4. This way when I add
additional sheets the order on my master will always be in date order

 

[Dave Peterson]

I should have read the suggested code:

Option Explicit
Sub testme()
Dim wCtr As Long
Dim rCtr As Long
rCtr = 1
For wCtr = Worksheets.Count To 1 Step -1
rCtr = rCtr + 1
With ActiveSheet.Cells(rCtr, 1)
.NumberFormat = "@" 'text
.Value = Sheets(wCtr).Name
End With
Next wCtr
End Sub

 

[srctr]

That worked great. Now this would be nice but I don't have to have it. Can
I eliminate the first 2 sheets from being put in the List - the Master sheet
and one other one.?

 

[Dave Peterson]

Are they the two leftmost?

For wCtr = Worksheets.Count To 3 Step -1

That worked great. Now this would be nice but I don't have to have it. Can
I eliminate the first 2 sheets from being put in the List - the Master sheet
and one other one.?

 

[srctr]

Yes they are. And this worked perfectly - so much better then always
building the formula to refer to each new sheet created.

Thanks so much