Array Function help
 

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")).Colu mns("A:Z").AutoFit

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

Array Function help
 

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
--
Regrds,
Tom Ogilvy

"DanQAEngineer" wrote in message
oups.com...
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")).Colu mns("A:Z").AutoFit

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

Array Function help
 

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

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Tom Ogilvy" wrote in message ...
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
--
Regrds,
Tom Ogilvy

"DanQAEngineer" wrote in message
oups.com...
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")).Colu mns("A:Z").AutoFit

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

Array Function help
 

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


"David McRitchie" wrote in message
...
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

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Tom Ogilvy" wrote in message
...
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
--
Regrds,
Tom Ogilvy

"DanQAEngineer" wrote in message
oups.com...
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")).Colu mns("A:Z").AutoFit

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

Array Function help
 

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)
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Tom Ogilvy" wrote in message ...
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


"David McRitchie" wrote in message
...
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

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Tom Ogilvy" wrote in message
...
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
--
Regrds,
Tom Ogilvy

"DanQAEngineer" wrote in message
oups.com...
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")).Colu mns("A:Z").AutoFit

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