collection of sheets

[mark]

Hi.

I'd like to define a collection of sheets that includes
every sheet in the workbook except the first sheet.

I recorded that, and it did it with an Array... Sheets
(Array("Sheet2", "Sheet3", "Sheet4")).select , for example.

But, when I wrote the program to determine the array, it
didn't work... in a couple of ways.

So, I tried it with a sheets variable defined as an array

but I'm having trouble with it.

there are a collection of files, to which I need to make
some changes to every sheet in the file, other than the
first sheet.

I'd like to do some of the changes in group mode, if
possible.

Is there a good way to do something like

Sheets(Sheet1!2:8).select ?


Thanks.

[mark]

Hello again.

If there is a simple way to do that, I'd be interested in
seeing it.

However, I did get one of the approaches I was working on,
to work.... define a string array with the sheet names,
then tell it to select that...

like this

stSheetNames(1) = "Sheet2"
stSheetNames(2) = "Sheet3"
stSheetNames(3) = "Sheet4"

sheets(stSheetNames).select

I set it to Option Base 1 and the assignment is done in a
loop with Redim Preserve, to make it automated for files
with a varying number of sheets... I just wrote it as
above for simplicity of communication.

But as I said, if anyone knows a simpler, better way, I'd
like to know, too.

Thanks,
Mark

[Tom Ogilvy]

That is the simpler better way. What's hard about that?

Just note that VBA provide very little support for working with grouped
sheets. If you are making changes manually, then that is another story.

--
Regards,
Tom Ogilvy

"mark" <[email protected]> wrote in message
news:[email protected]...
> Hello again.
>
> If there is a simple way to do that, I'd be interested in
> seeing it.
>
> However, I did get one of the approaches I was working on,
> to work.... define a string array with the sheet names,
> then tell it to select that...
>
> like this
>
> stSheetNames(1) = "Sheet2"
> stSheetNames(2) = "Sheet3"
> stSheetNames(3) = "Sheet4"
>
> sheets(stSheetNames).select
>
> I set it to Option Base 1 and the assignment is done in a
> loop with Redim Preserve, to make it automated for files
> with a varying number of sheets... I just wrote it as
> above for simplicity of communication.
>
> But as I said, if anyone knows a simpler, better way, I'd
> like to know, too.
>
> Thanks,
> Mark

[Jim Thomlinson]

How do you know which is the first sheet? If someone moves it around or
changes the name then you might be hooped... otherwis you can use something
like this to loop through all of the sheets...

dim wks as worksheet

for each wks in worksheets
if wks.name <> "Whatever" then
msgbox wks.name
endif
next wks

HTH
"mark" wrote:

> Hello again.
>
> If there is a simple way to do that, I'd be interested in
> seeing it.
>
> However, I did get one of the approaches I was working on,
> to work.... define a string array with the sheet names,
> then tell it to select that...
>
> like this
>
> stSheetNames(1) = "Sheet2"
> stSheetNames(2) = "Sheet3"
> stSheetNames(3) = "Sheet4"
>
> sheets(stSheetNames).select
>
> I set it to Option Base 1 and the assignment is done in a
> loop with Redim Preserve, to make it automated for files
> with a varying number of sheets... I just wrote it as
> above for simplicity of communication.
>
> But as I said, if anyone knows a simpler, better way, I'd
> like to know, too.
>
> Thanks,
> Mark
>

[mark]

>That is the simpler better way. What's hard about that?

You're right, Tom. That's not all that hard. I just
started out in the wrong direction... I have worked with
the Sheets(i) collection a lot, but not with multiple
sheets at one time, and was trying to access them in a few
ways that didn't work.

At first I was looking for something more like the Range
object... Range(cell1,cell2)... I was looking for
something like Sheets(Sheet1,Sheet2) with a
activeworkbook.sheets.count to determine the ending sheet.

The array, as described earlier, will work fine.

[mark]

>How do you know which is the first sheet? If someone
moves it around or changes the name then you might be
hooped...



It's a lot of sheets involved, but only 23 files. The
first sheet. The first sheet is not to be worked with, in
each case.

This isn't a production job, it's a one time edit to all
of the detail sheets, which I am programming, because we
all know that 'one time edits' tend to be needed many
times.

But it's not scheduled to run more.

[mark]

>Just note that VBA provide very little support for
working with grouped sheets.

Yep, I sure do see what you mean.

Thanks for the warning.