Worksheets names in cells

MJA

Board Regular
Joined
Feb 18, 2002
Messages
79
Hi everybody,

I want my worksheets names to appear in
cells A1, A2 ....
Can I do this with a formula instead of VB.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
In VBA, this code will work: -

Public Sub SheetCount()

Dim ws As Worksheet
x = 1
For Each ws In Worksheets
Worksheets(1).Range("A" & x).Value = ws.Name
x = x + 1
Next ws

End Sub

There is a worksheet function to do this, I think Aladin Akyurek posted an answer to a similar question once, but I can't for the life of me remember what it was.
 
Upvote 0
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,
LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

Will get you the name of the active sheet. Anyone help offsetting the sheets (obviously placing "worksheet name!" infront of a1 will work, but it's manual)?

Could put this on each sheet and do some if statements on your main page.

Incidentally, the file needs to be saved for this to work.

Cheers,

Nate
This message was edited by NateO on 2002-02-22 16:19
 
Upvote 0
Well, here's my offset. Bury this in your visual basic editor (you won't have to touch it after that):

Function SHEETOFFSET(offset)
Application.Volatile
SHEETOFFSET = Sheets(Application.Caller.Parent.Index _
+ offset).Name
End Function

Now, if your first sheet is going in row 1 then down, use the following formula:

=IF(ISERR(sheetoffset(ROW()-1)=TRUE),"",sheetoffset(ROW()-1))

If it's in row 2:

=IF(ISERR(sheetoffset(ROW()-2)=TRUE),"",sheetoffset(ROW()-2))

etc......It will show "" if there's no more sheets to show.

Incidentally, if you want to go across instead of down switch:

row with column

Have a great weekend. Cheers,

Nate
This message was edited by NateO on 2002-02-22 20:12
 
Upvote 0
Seems awfully complicated to me. Assuming I'm understanding your query correctly, simply code: =CELL("filename",A1). HTH.
 
Upvote 0
My responses do not include filepath & filename. It depends on what you want...

My second response shows how to get all of the sheets on one sheet, from a variable perspective, versus just the sheet you're on/in.

Like most things in life, it probably is more complicated than it ought to be.

fyi: The iserr kills the $&#* you get if you copy the formula 1000 rows deep and you only have 4 worksheets, etc....

Cheers, Nate
This message was edited by NateO on 2002-02-22 19:52
 
Upvote 0
On 2002-02-22 11:47, NateO wrote:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,
LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

Good one Nate, I was after this the other day but couldn't find it :)
 
Upvote 0
Chris, it's a good one...used this one the other week.

How's the book coming along? I was surprised to see Jim ripping up xl 2002 because he didn't like the fact the you can't put the filepathname in the footer automatically. A surprisingly easy task in vba. He didn't mention the ability to use the "speak" command which looks pretty neato.

Have a good one. Cheers, Nate
This message was edited by NateO on 2002-02-23 13:24
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top