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

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" <(E-Mail Removed)> wrote in message
news:6EEAD7EC-2D4A-4E02-8E87-(E-Mail Removed)...
> 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

Hi

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


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"karimhemani" <(E-Mail Removed)> wrote in message
news:6EEAD7EC-2D4A-4E02-8E87-(E-Mail Removed)...
> 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