Sheet name
Casbhu
can I get the sheet name and place it in a cell for all sheets in the
workbook without referencing the activesheet method? all I want is to place
the sheetname in a cell.
Jerry Hunt
Here's a simple little VBA module to do just that. Note, it only deals
with worksheets (modules, dialog sheets and chart sheets in the same
workbook don't bother it). Thus sub places the sheet name in cell A1.
Change the "A1" in w.[A1] to the cell address you want.
Sub GetSheetNames()
For Each w In Worksheets
w.[A1].Value = w.Name
Next w
End Sub
--
Spam buster:
To send me e-mail, remove the "hello..." from my e-mail address (I
hate spambots).
Alan Beban
emailed and posted
I couldn't quite tell exactly what you were trying to describe. The first
of the following two Sub Procedures puts a list of all Sheet names
(worksheets and other sheets) in Column A of the first worksheet starting
at A1. The second one puts the name of each Worksheet into Cell A1 of that
worksheet.
Sub test3()
For Each sh In Sheets
i = i + 1
Worksheets(1).Range("A" & i).Value = sh.Name
Next
End Sub
Sub test4()
For Each sh In Worksheets
sh.[a1].Value = sh.Name
Next
End Sub
Good luck,
Alan Beban be...@pacbell.net
cas...@aol.com (Casbhu) wrote in article
<19970916172...@ladder02.news.aol.com>...
Sam The Sham
Casbhu,
Yes you can. The following sub will do that for you.
Sub Get_Names()
For i = 1 To Worksheets.Count
ActiveCell = Sheets(1).Name
ActiveCell.Offset(1, 0).Select
Next
End Sub
Casbhu <cas...@aol.com> wrote in article
John Green
In article <19970916172...@ladder02.news.aol.com> on Tue, 16
Sep 1997 19:23:03 +0200 "cas...@aol.com (Casbhu)" <cas...@aol.com>
wrote:
<< can I get the sheet name and place it in a cell for all sheets in the
workbook without referencing the activesheet method? all I want is to
place
the sheetname in a cell. >>
In Excel 95 and 97 you can use the following worksheet formula to get
the sheet name. No macro code is required:
=MID(CELL("FileName"),FIND("]",(CELL("FileName")))+1,999)
If you want the full workbook reference, including path, just use:
=CELL("FileName")
Note that the workbook must be saved for the above to work.
Hope this helps,
John Green
Sydney
Australia
Microsoft MVP - Excel
Bill Manville
>
>If you want the full workbook reference, including path, just use:
>
>=CELL("FileName")
>
G'day John
I recommend
=CELL("FileName",A1)
which is less likely to fail if a different sheet is active when you
recalculate
Bill Manville
Oxford, England
Microsoft Excel - MVP
John Green
G'Day Bill,
Good point,