The code below will list all the sheet names in you workbook to column B and
add the !

Sub getsheetnames()
x = 1
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Worksheets("Sheet1").Cells(x, 2) = ws.Name + "!"
x = x + 1
Next ws
End Sub

Mike

"pablo bellissimo" wrote:

Thanks for the reply.

Is there a way for Excel to find the sheet names without me typing them
though as there will be a large number of sheets.



"Gary''s Student" wrote:

Very easy:

In an unused column, say column B, enter your sheet names exclaimed:

Sales!
Revenue!

The in A1 enter:
=INDIRECT(B1 & "A1")
and copy down

--
Gary''s Student
gsnu200709


"pablo bellissimo" wrote:

Hi All is there a way for me to drag down a formula that will change the
sheet it is looking at in each new cell?

lets say that in cell A1 the formula is =MySheet2!A1

As I replicate this down I want the sheet2 to increase to MySheet3, then
MySheet4 etc. The sheets might not be called 2,3,4,5 etc but I need the
formula to just go to the next sheet regardless of the name of it.

I really hope this makes sense!

Thanks again guys