return all worksheet tab names and chart sheet tab names in report - an example

[DataFreakFromUtah]

No question here, just a procedure for the archive.

Search criteria: worksheet tab names report return worksheet tab names
return tab names return worksheet chart names return tab names
return chart sheet names report sheet tab names report chart names
return all worksheet names return all chart sheet names return all chart names
list all tab names list all worksheet names list all chart sheet names list all
chart names


Sub SheetTabAndChartTabNamesReport()

'Return all worksheet tab and chart tab names to a new worksheet
On Error Resume Next
ActiveWorkbook.Worksheets.Add.Name = "WkSheetNamesReport"
Sheets("WkSheetNamesReport").Select
Range("A1").Select
Dim iSheet As Long
Dim iChart As Long
For iSheet = 1 To ActiveWorkbook.Worksheets.Count
ActiveCell.Offset(iSheet - 1, 0) = "'" & Worksheets(iSheet).Name
Next iSheet

For iChart = 1 To ActiveWorkbook.Charts.Count
ActiveCell.Offset(iChart - 1, 0) = "'" & Charts(iChart).Name
Next iChart


End Sub

 

[Tom Ogilvy]

Won't the Chart names overwrite the Sheet names?

Wouldn't you want to exclude the name "WkSheetNamesReport"

 

[Norman Jones]

Hi DFFU,

Since you offer your procedure for Archive purposes, and uniquely in the
spirit of constructive criticism, allow me to make some
(non-exhaustive)observations.

You use On Error Resume Next to trap the error which arises if a sheets
named WkSheetNamesReport already exists. You should restore the default by
adding an On Error Goto 0 line once the error trap has served its purpose.

You explicitly select the newly inserted sheet but this is unnecessary as
the inserted sheet is the active sheet. On adding the report sheet, you
might wish to consider adding as the first (or last) sheet. You might
additionally wish to exclude the report sheet from the list, either by name
position.

Your selection of cell A1 is unnecessary. Selections are rarely necessary
and should be avoided, if possible.

You loop through the worksheets collection and the loop through the charts
collection. You could reduce this to a single loop of the Sheets collection,
which would have the incidental advantage of picking up sheets which are
neither worksheets nor charts.

Because of the way you use your charts loop to write to the report sheet,
all chart information will overwrite all or part of the worksheets report
information. The single loop arrangement would obviate this without further
intervention