Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
return all worksheet tab names and chart sheet tab names in report - an example
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
return all worksheet tab names and chart sheet tab names in report - an example
Won't the Chart names overwrite the Sheet names?
Wouldn't you want to exclude the name "WkSheetNamesReport" -- Regards, Tom Ogilvy "DataFreakFromUtah" wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
return all worksheet tab names and chart sheet tab names in report - an example
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 --- Regards, Norman "DataFreakFromUtah" wrote in message om... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart worksheet names keep resetting | Charts and Charting in Excel | |||
using the Excel generic worksheet names instead of user-given names in code | Excel Discussion (Misc queries) | |||
MAKE A LIST OF NAMES FROM REPEATED NAMES IN THE SAME WORKSHEET | Excel Discussion (Misc queries) | |||
Disappearing Chart Worksheet Names | Excel Discussion (Misc queries) | |||
Copying a sheet with a chart that uses Excel Names | Charts and Charting in Excel |