Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 19th 07, 07:08 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 6
Default Cycle through multiple sheets

Hello all,

Basically I need code to get the names of the sheets so that I can have a
macro cycle through each sheet of a workbook and do the formatting I want.

Each workbook I have has a different number of sheets and each sheet is
named something different. Theres no way I can predict the names of the
sheets. The macro would be run each time a new workbook is opened so I don't
have to worry about the macro going cross-workbooks or opening a different
workbook. Its all done within the same workbook among the various number of
sheets in each workbook.

My initial idea was to somehow get the names of the sheets and put it into
an array. Then use a 'for' loop to cycle through the array and select each
sheet.

But I don't know how to get the list of each sheet or select the next sheet
without knowing the name of the sheet.

Any help or guidance or code samples would be appreciated!

-Karim

  #2   Report Post  
Old March 19th 07, 07:18 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,391
Default Cycle through multiple sheets

Karim,
As with many aspect of the Excel Object Model, you can use the collection of
the desired objects, without knowing each by name.

dim XLWS as Worksheet

For each xlws in thisworkbook.worksheets
debug.print xlws.name 'Whatever you code does
next

NickHK

"karimhemani" wrote in message
...
Hello all,

Basically I need code to get the names of the sheets so that I can have a
macro cycle through each sheet of a workbook and do the formatting I want.

Each workbook I have has a different number of sheets and each sheet is
named something different. Theres no way I can predict the names of the
sheets. The macro would be run each time a new workbook is opened so I

don't
have to worry about the macro going cross-workbooks or opening a different
workbook. Its all done within the same workbook among the various number

of
sheets in each workbook.

My initial idea was to somehow get the names of the sheets and put it into
an array. Then use a 'for' loop to cycle through the array and select each
sheet.

But I don't know how to get the list of each sheet or select the next

sheet
without knowing the name of the sheet.

Any help or guidance or code samples would be appreciated!

-Karim



  #3   Report Post  
Old March 19th 07, 07:18 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 510
Default Cycle through multiple sheets

Hi

.....
For Each ws In Worksheets
... your code here
Next ws
.....


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"karimhemani" wrote in message
...
Hello all,

Basically I need code to get the names of the sheets so that I can have a
macro cycle through each sheet of a workbook and do the formatting I want.

Each workbook I have has a different number of sheets and each sheet is
named something different. Theres no way I can predict the names of the
sheets. The macro would be run each time a new workbook is opened so I
don't
have to worry about the macro going cross-workbooks or opening a different
workbook. Its all done within the same workbook among the various number
of
sheets in each workbook.

My initial idea was to somehow get the names of the sheets and put it into
an array. Then use a 'for' loop to cycle through the array and select each
sheet.

But I don't know how to get the list of each sheet or select the next
sheet
without knowing the name of the sheet.

Any help or guidance or code samples would be appreciated!

-Karim





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
How do I make a graph with 2-cycle X 3-cycle log-log graph paper? Charles A. Wilson Charts and Charting in Excel 1 December 17th 09 04:03 AM
how do i set up attendance sheets for 2 week repeting work cycle bill Excel Discussion (Misc queries) 0 January 5th 09 03:58 PM
crtl + page down or up does not cycle through sheets in excel 2007 John G.[_2_] Excel Discussion (Misc queries) 4 May 8th 08 05:21 PM
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA Amaxwell Excel Worksheet Functions 4 August 17th 06 06:23 AM
How do I keep result from 1 iteration cycle to use in next cycle? sgl8akm Excel Discussion (Misc queries) 0 July 27th 06 08:28 PM


All times are GMT +1. The time now is 04:32 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