List all Sheets onto one sheet

[mroseto]

Hello all,

I would like to have a list on a seperate sheet that lists all the other sheets in the workbook.

So if I have sheets named (tabs ,apples, oranges, grapes)

I would have a list on tabs that says, apples, oranges, grapes.

Hope this makes sense, if not let me know and I will try to clarify.

Thanks

 

[BrianM]

You might want to try this:

http://www.mrexcel.com/tip017.shtml

 

[mroseto]

Thanks for the quick response.

I looked at that, but honestly I have no clue how to make it into something I need.

Maybe it would be easier if I reference a specific cell on each sheet. The name is in the same spot on each one, would this make more sense?

Thanks again.

 

[mbb4331]

You could also try this:

Sub test()

Dim sht As Worksheet

For Each sht In ThisWorkbook.Sheets
ThisWorkbook.Sheets("Sheet1").Cells(sht.Index, 1) = sht.Name
Next sht

End Sub

 

[mroseto]

That worked perfectly. Thanks

 

[mroseto]

Sorry thought that was what I needed. I now need to reference certain cells on each corresponding sheet. How can that be done?

What I want is a list with
Item Sales Costs
Apples 5 3
Oranges 18 12
Grapes 20 15

This information is on the same place on every sheet.

Thanks again for your help

 

[mbb4331]

This assumes Sheet1 is the summary sheet, and Sales info is in cell A1 of each sheet and Costs info is in cell A2.

Sub test()

Dim sht As Worksheet

For Each sht In ThisWorkbook.Sheets
If sht.Name = "Sheet1" Then
Else
ThisWorkbook.Sheets("Sheet1").Cells(sht.Index, 1) = sht.Name
ThisWorkbook.Sheets("Sheet1").Cells(sht.Index, 2) = ThisWorkbook.Sheets(sht.Index).Range("A1")
ThisWorkbook.Sheets("Sheet1").Cells(sht.Index, 3) = ThisWorkbook.Sheets(sht.Index).Range("A2")
End If

Next sht

End Sub

 

[mroseto]

Perfect.

Thanks a bunch