Get sheet names

J

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.
 
M

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
 
J

JHL

Mike H

PERFECT! Thanks.

Mike H said:
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
 
S

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?
 
S

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
 
D

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
 
S

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.?
 
D

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.?
 
S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top