Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 6th 04, 06:30 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2003
Posts: 36
Default 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   Report Post  
Old October 6th 04, 07:59 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 27,285
Default 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   Report Post  
Old October 6th 04, 08:09 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,302
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Chart worksheet names keep resetting dloomer Charts and Charting in Excel 0 December 10th 09 05:58 PM
using the Excel generic worksheet names instead of user-given names in code Paul Excel Discussion (Misc queries) 5 June 26th 09 08:44 PM
MAKE A LIST OF NAMES FROM REPEATED NAMES IN THE SAME WORKSHEET r.kordahi Excel Discussion (Misc queries) 2 January 3rd 09 08:10 AM
Disappearing Chart Worksheet Names wiubiblio Excel Discussion (Misc queries) 1 July 24th 08 04:13 PM
Copying a sheet with a chart that uses Excel Names Holger Gerths Charts and Charting in Excel 2 October 16th 07 11:41 AM


All times are GMT +1. The time now is 07:29 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017