Array Function help

[DanQAEngineer]

I want three sheets to autofit certain columns. Here is the code that I
currently have in my script... ugly.

Worksheets("Sheet1").Columns("A:Z").AutoFit
Worksheets("Sheet2").Columns("A:Z").AutoFit
Worksheets("Sheet3").Columns("A:Z").AutoFit

I want to try something more like:

Worksheets(Array("Sheet1","Sheet2","sheet3")).Columns("A:Z").AutoFit

I'm getting an error on this code, though. Thanks in advance for
helping a beginner.

 

[Tom Ogilvy]

Generally excel doesn't support grouped sheets which is what you are trying
to do.

for i = 1 to 3: worksheets("Sheet" & i).Columns("A:Z").Autofit: next

 

[David McRitchie]

Hi Dan,
Tom answered your question for the specific sheets, but when I
read Tom's answer I was thinking it referred to grouped sheets.
Anyway it might be well to include information for grouped sheets
as well. Of course it won't make the code any more beautiful, but it
may help make things more generic for similar questions.

To loop through the grouped sheets. See the additional
coding for looping through selectedsheets by Gary L. Brown within
the insert row macro
Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

 

[Tom Ogilvy]

My answer did refer to grouped sheets.

but there really is no reason to group them

v = Array("Sheet1", "Sheet5", "Sheet8")
worksheets(v).Select
for each sh in activeWindow.Selectedsheets

Next

just adds code. Just go straight to
v = Array("Sheet1", "Sheet5", "Sheet8")
for i = lbound(v) to ubound(v)
set sh = worksheets(v(i))

Next

--
Regards,
Tom Ogilvy

Hi Dan,
Tom answered your question for the specific sheets, but when I
read Tom's answer I was thinking it referred to grouped sheets.
Anyway it might be well to include information for grouped sheets
as well. Of course it won't make the code any more beautiful, but it
may help make things more generic for similar questions.

To loop through the grouped sheets. See the additional
coding for looping through selectedsheets by Gary L. Brown within
the insert row macro
Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

 

[David McRitchie]

I guess I always think of "grouped sheets" as being preselected
within Excel before invoking a macro. I certainly agree that
it would not be advantageous to group the sheets within a
macro just to be able to use code that handles grouped sheets.

I can't find the term "grouped sheets" in help even though "ungroup sheets" is the first menu item when you right click on group
sheets with the
Ctrl key. (Excel 2000)