How do I print a list of worksheet tab names in a workbook
 

I can view a list of the worksheet tabs using the properties command and
viewing the contents tab, however sometimes I need to print a list of the tab
names. To do this I use the Print Screen. This is slow and must be done
several times to include all the tab names. Is there a way of printing this
list any faster?

Try a macro like this:

Sub PrintWSNames()
Dim sh As Worksheet
Dim i As Long

With ActiveWorkbook
.Worksheets(1).Select
Set sh = .Worksheets.Add
End With

With sh
For i = 2 To ActiveWorkbook.Worksheets.Count
.Cells(i, "A").Value = Worksheets(i).Name
Next i
.Cells(1, "A").Value = "Sheet " & _
"Names (excl. this one)"

[A:A].EntireColumn.AutoFit

.PrintOut Copies:=1, Collate:=True
End With

End Sub

---
HTH
Jason
Atlanta, GA

-----Original Message-----
I can view a list of the worksheet tabs using the
properties command and
viewing the contents tab, however sometimes I need to
print a list of the tab
names. To do this I use the Print Screen. This is slow
and must be done
several times to include all the tab names. Is there a
way of printing this
list any faster?
.

Jason, I like your macro and succesfully "ran" it. How can I
adjust it to list the tabs which have charts? I tried to replace
"Worksheets" with "Charts" but ran into an error at ".Cells"

Sub PrintCHNames()
Dim ch As Chart
Dim i As Long

With ActiveWorkbook
.Charts(1).Select
Set ch = .Charts.Add
End With

With ch
For i = 2 To ActiveWorkbook.Charts.Count
.Cells(i, "A").Value = Charts(i).Name
Next i
.Cells(1, "A").Value = "Sheet " & "Names (excl. this one)"

[A:A].EntireColumn.AutoFit

..PrintOut Copies:=1, Collate:=True

End With
End Sub

Thanks in advance, Jason

"Jason Morin" wrote:

Try a macro like this:

Sub PrintWSNames()
Dim sh As Worksheet
Dim i As Long

With ActiveWorkbook
.Worksheets(1).Select
Set sh = .Worksheets.Add
End With

With sh
For i = 2 To ActiveWorkbook.Worksheets.Count
.Cells(i, "A").Value = Worksheets(i).Name
Next i
.Cells(1, "A").Value = "Sheet " & _
"Names (excl. this one)"

[A:A].EntireColumn.AutoFit

.PrintOut Copies:=1, Collate:=True
End With

End Sub

---
HTH
Jason
Atlanta, GA

-----Original Message-----
I can view a list of the worksheet tabs using the
properties command and
viewing the contents tab, however sometimes I need to
print a list of the tab
names. To do this I use the Print Screen. This is slow
and must be done
several times to include all the tab names. Is there a
way of printing this
list any faster?
.

John

Try this variation to get both worksheets and chart sheets listed.

Private Sub ListSheets()
'list of sheet names starting at A1
Dim Rng As Range
Dim i As Integer
Worksheets.Add
Set Rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
Rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub

See VBE Help under "Sheets Collection Object"


Gord Dibben Excel MVP



On Wed, 2 Mar 2005 11:31:09 -0800, "JohnL"
wrote:

Jason, I like your macro and succesfully "ran" it. How can I
adjust it to list the tabs which have charts? I tried to replace
"Worksheets" with "Charts" but ran into an error at ".Cells"

Sub PrintCHNames()
Dim ch As Chart
Dim i As Long

With ActiveWorkbook
.Charts(1).Select
Set ch = .Charts.Add
End With

With ch
For i = 2 To ActiveWorkbook.Charts.Count
.Cells(i, "A").Value = Charts(i).Name
Next i
.Cells(1, "A").Value = "Sheet " & "Names (excl. this one)"

[A:A].EntireColumn.AutoFit

.PrintOut Copies:=1, Collate:=True

End With
End Sub

Thanks in advance, Jason

"Jason Morin" wrote:

Try a macro like this:

Sub PrintWSNames()
Dim sh As Worksheet
Dim i As Long

With ActiveWorkbook
.Worksheets(1).Select
Set sh = .Worksheets.Add
End With

With sh
For i = 2 To ActiveWorkbook.Worksheets.Count
.Cells(i, "A").Value = Worksheets(i).Name
Next i
.Cells(1, "A").Value = "Sheet " & _
"Names (excl. this one)"

[A:A].EntireColumn.AutoFit

.PrintOut Copies:=1, Collate:=True
End With

End Sub

---
HTH
Jason
Atlanta, GA

-----Original Message-----
I can view a list of the worksheet tabs using the
properties command and
viewing the contents tab, however sometimes I need to
print a list of the tab
names. To do this I use the Print Screen. This is slow
and must be done
several times to include all the tab names. Is there a
way of printing this
list any faster?
.