List all Sheets onto one sheet

mroseto

Board Regular
Joined
Jul 18, 2002
Messages
203
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

mroseto

Board Regular
Joined
Jul 18, 2002
Messages
203
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

Board Regular
Joined
Dec 22, 2005
Messages
152
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

Board Regular
Joined
Jul 18, 2002
Messages
203

ADVERTISEMENT

That worked perfectly. Thanks
 

mroseto

Board Regular
Joined
Jul 18, 2002
Messages
203
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

Board Regular
Joined
Dec 22, 2005
Messages
152
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,096
Messages
5,545,926
Members
410,713
Latest member
TaremyLunsil
Top