Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array function - I think! | Excel Worksheet Functions | |||
OR function in array-entered IF function | Excel Worksheet Functions | |||
Array Function | Excel Worksheet Functions | |||
#DIV/0! in the array function | Excel Worksheet Functions | |||
UDF Array function | Excel Programming |