Retrieving all worksheet names into a new worksheet

[Guest]

I'd like to retrieve all worksheet names (I have >30 in one workbook) and
list them on a separate worksheet. I've used a macro in the past which did
this, but I'm unable to locate it. Can someone assist?

Thanks in advance,
Barb Reinhardt

 

[Paul B]

Barb, give this a try,

Sub List_All_Sheets()

Dim Rng As Range

Dim i As Integer

Worksheets.Add

Set Rng = Range("A1")

For Each Sheet In ActiveWorkbook.Sheets

Rng.Offset(i, 0).Value = Sheet.Name

i = i + 1

Next Sheet

End Sub
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Guest]

That did it, thanks!

Barb, give this a try,

Sub List_All_Sheets()

Dim Rng As Range

Dim i As Integer

Worksheets.Add

Set Rng = Range("A1")

For Each Sheet In ActiveWorkbook.Sheets

Rng.Offset(i, 0).Value = Sheet.Name

i = i + 1

Next Sheet

End Sub
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Paul B]

Your welcome
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Boss]

Great code,

But along with the sheet name if i wish to get cell vaule "c10" then?

I need this as i have to check about 250 sheets. please help, us macro can
solve my purpose.

Thanks!

 

[DataHog]

Adding a new worksheet for listing worksheet names & cell C10 values, adding
the worksheet names in Column A and adding the value in cell C10 in column B
for every worksheet in the workbook.

-------------------------------------
Sub List_All_SheetNames()
Dim Rng As Range
Dim i As Integer
Worksheets.Add
Cells(1, 1).Value = "Worksheet Names"
Cells(1, 2).Value = "Cell C10"
Set Rng = Range("A2")

For Each Sheet In ActiveWorkbook.Sheets
Rng.Offset(i, 0).Value = Sheet.Name
Rng.Offset(i, 1).Value = Worksheets(Sheet.Name).Range("C10").Value
i = i + 1
Next Sheet
Columns("A:B").EntireColumn.AutoFit
End Sub