I would like in column A to display names of all my worksheets.

  • Thread starter Thread starter Marvin Hlavac
  • Start date Start date
M

Marvin Hlavac

Hi All,

I would like in column A to display names of all my worksheets.

In this workbook I insert a new worksheet each working day (5 or more days a
week). Each sheet is named example: 11-03-2003, 11-04-2003... I would now
like to create a summary worksheet in this book where I would in column A
display all worksheet names. Each day when I insert a new sheet into this
book this summary sheet should automatically put this newly created sheet
name in the A column.

Is it possible?

Thanks in advance for any help.
 
There's no automatic way to do that.

You might want to check out my PUP v5 add-in (at my web site). One of the
utilities will create a workbook table of contents that lists each sheet in
the workbook (with a hyperlinks to the sheets). However, it's not automatic.
You'll need to run the macro to update the contents sheet.

You can download a 30-day trial version to see if it meets your needs.

John Walkenbach
For Excel tips, macros, & downloads...
http://j-walk.com/ss
 
Marvin, this is not automatic, but you could run it at any time and it will
make a sheet with all the sheet names and create hyper links to them, will
update the sheet the next time you run it, not my code, but I don't remember
where it came from to give them the credit

Sub WorksheetNamesWithHyperLink()
Dim iRow As Integer, iColumn As Integer
Dim i As Integer, x As Integer, iWorksheets As Integer
Dim objOutputArea As Object
Dim StrTableName As String, StrWorkSheetName As String

StrTableName = "Worksheet Names Table"

'Check for duplicate Worksheet name
i = ActiveWorkbook.Sheets.Count
For x = 1 To i
If Windows.Count = 0 Then Exit Sub
If UCase(Worksheets(x).Name) = UCase(StrTableName) Then
Worksheets(x).Activate
If Err.Number = 9 Then
Exit For
End If
Application.DisplayAlerts = False 'turn warning messages
off
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True 'turn warning messages
on
'Exit Sub
End If
Next

'Add new worksheet at end of workbook
' where results will be located
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)

'Name the new worksheet and set up Titles
ActiveWorkbook.ActiveSheet.Name = StrTableName
ActiveWorkbook.ActiveSheet.Range("A1").Value = "Worksheet List"

'Count number of worksheets in workbook
iWorksheets = ActiveWorkbook.Sheets.Count

'Initialize row and column counts for putting info into StrTableName
sheet
iRow = 1
iColumn = 0

Set objOutputArea = ActiveWorkbook.Sheets(StrTableName).Range("A1")

'Check Sheet names
For x = 1 To iWorksheets
Sheets(x).Activate
StrWorkSheetName = ActiveSheet.Name
'put information into StrTableName worksheet
With objOutputArea
.Offset(iRow, iColumn) = " " & StrWorkSheetName
'create hyperlink
ActiveSheet.Hyperlinks.Add anchor:=objOutputArea.Offset(iRow,
iColumn), _
Address:="", _
SubAddress:=Chr(39) & StrWorkSheetName & Chr(39) & "!A1"
iRow = iRow + 1
End With
Next x

'format StrTableName worksheet
Range("A2").Select
ActiveWindow.FreezePanes = True
Range("A1").Font.Bold = True
Columns("A:A").EntireColumn.AutoFit

End Sub



--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
Assumes that the worksheet that will contain the sheet names is called
"Summary"; that Range("A1") contains a label (like "Sheet Names").

Each time that you add a sheet, it will be named to the date and the
new name will be placed in the 'Summary' worksheet.

(1) Alt F11 to bring up VBE.
(2) Ctrl R to bring up Project Explorer. Find your project.
(3) Double click on ThisWorkbook module.
(4) Place the following code snippet in the module.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sh.Name = Format(Date, "mm-dd-yyyy")
Sheets("Summary").Cells(Rows.Count, 1).End(xlUp). _
Offset(1, 0).Value = Sh.Name
End Sub

HTH
Paul
 
Hi Paul, Paul, John,

Thanks all three of you for the solutions. I'm going to try them right away.

This NG is such a helpful and friendly community. I've been reading and
learning here daily for the past three or four weeks. Thanks to everyone
here.
 
Back
Top