Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting PageSetup Properties in Multiple Worksheets
Right now my VBA code creates a number of worksheets and then sets a bunch
of their PageSetup properties. Because PageSetup can be so slow, I would like to set all of the properties at once. I tried: With Sheets(Array("Sheet2", "Sheet1")).PageSetup .LeftHeader = "foo" ... End With But I get a run-time error on the With statement (438 Object doesn't support this property or method). Is there some other way to accomplish this or to dynamically (and temporarily) define what the default PageSetup properties should be for newly added worksheets? TIA, josh |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting PageSetup Properties in Multiple Worksheets
Hello Josh,
Get yourself a test workbook with about 50 or more sheets in it, select all the sheets and then add some header/footer info. My experience has been that Excel acts like a snake trying to swallow a goat when doing this. It wouldn't be any better using code. Do one sheet at a time but to reduce the time required... 1. Only change/add those properties you require. 2. Do not leave or have other lines in the code that refer to pagesetup properties. 3. Set displaypagebreaks to false at the start of your code. 4. If you have quite a bit of extra time, look at converting your pagesetup code to XL4 macro code. It is slightly faster. John Green has a almost complete example here... (01/22/2001, "About PageSetup", Excel.programming) http://makeashorterlink.com/?Q606527BC Regards, Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Josh Sale" <jsale@tril dot cod wrote in message ... Right now my VBA code creates a number of worksheets and then sets a bunch of their PageSetup properties. Because PageSetup can be so slow, I would like to set all of the properties at once. I tried: With Sheets(Array("Sheet2", "Sheet1")).PageSetup .LeftHeader = "foo" End With But I get a run-time error on the With statement (438 Object doesn't support this property or method). Is there some other way to accomplish this or to dynamically (and temporarily) define what the default PageSetup properties should be for newly added worksheets? TIA, josh |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting PageSetup Properties in Multiple Worksheets
How about just looping through the worksheets...
dim wks as worksheet for each wks in worksheets(array("sheet1","sheet2")) with wks.pagesetup ... end with next wks Josh Sale wrote: Right now my VBA code creates a number of worksheets and then sets a bunch of their PageSetup properties. Because PageSetup can be so slow, I would like to set all of the properties at once. I tried: With Sheets(Array("Sheet2", "Sheet1")).PageSetup .LeftHeader = "foo" ... End With But I get a run-time error on the With statement (438 Object doesn't support this property or method). Is there some other way to accomplish this or to dynamically (and temporarily) define what the default PageSetup properties should be for newly added worksheets? TIA, josh -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting PageSetup Properties in Multiple Worksheets
Set it for one sheet, then copy that sheet instead of adding sheets.
Also lookin xl help for templates customizing workbook defaults also Create a sheet template for new worksheets See Sheet.xlt If you create a single sheet workbook and name it sheet.xlt (after you format it the way you want), then save it in the xlStart directory, then insert = WorkSheet -- Regards, Tom Ogilvy "Josh Sale" <jsale@tril dot cod wrote in message ... Right now my VBA code creates a number of worksheets and then sets a bunch of their PageSetup properties. Because PageSetup can be so slow, I would like to set all of the properties at once. I tried: With Sheets(Array("Sheet2", "Sheet1")).PageSetup .LeftHeader = "foo" ... End With But I get a run-time error on the With statement (438 Object doesn't support this property or method). Is there some other way to accomplish this or to dynamically (and temporarily) define what the default PageSetup properties should be for newly added worksheets? TIA, josh |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting PageSetup Properties in Multiple Worksheets
for Code, KeepItCool post this method. Format a sheet. Group all the
sheets making it the first sheet in the group. The use sendkeys: ' sheet3 is the pre-formatted sheet sheets(array("sheet2","sheet3","sheet4")).select sheets("sheet3").activate SendKeys "{enter}" Application.Dialogs(xlDialogPageSetup).Show keepITcool As I recall, this doesn't do every setting, but most of the more common ones. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Set it for one sheet, then copy that sheet instead of adding sheets. Also lookin xl help for templates customizing workbook defaults also Create a sheet template for new worksheets See Sheet.xlt If you create a single sheet workbook and name it sheet.xlt (after you format it the way you want), then save it in the xlStart directory, then insert = WorkSheet -- Regards, Tom Ogilvy "Josh Sale" <jsale@tril dot cod wrote in message ... Right now my VBA code creates a number of worksheets and then sets a bunch of their PageSetup properties. Because PageSetup can be so slow, I would like to set all of the properties at once. I tried: With Sheets(Array("Sheet2", "Sheet1")).PageSetup .LeftHeader = "foo" ... End With But I get a run-time error on the With statement (438 Object doesn't support this property or method). Is there some other way to accomplish this or to dynamically (and temporarily) define what the default PageSetup properties should be for newly added worksheets? TIA, josh |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting PageSetup Properties in Multiple Worksheets
Thanks Jim.
I was aware of your suggestions and have already implemented suggestions 1 - 3. I've not taken the step of doing the pagesetup in XL4 but I just might need to. Thanks again, josh "Jim Cone" wrote in message ... Hello Josh, Get yourself a test workbook with about 50 or more sheets in it, select all the sheets and then add some header/footer info. My experience has been that Excel acts like a snake trying to swallow a goat when doing this. It wouldn't be any better using code. Do one sheet at a time but to reduce the time required... 1. Only change/add those properties you require. 2. Do not leave or have other lines in the code that refer to pagesetup properties. 3. Set displaypagebreaks to false at the start of your code. 4. If you have quite a bit of extra time, look at converting your pagesetup code to XL4 macro code. It is slightly faster. John Green has a almost complete example here... (01/22/2001, "About PageSetup", Excel.programming) http://makeashorterlink.com/?Q606527BC Regards, Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Josh Sale" <jsale@tril dot cod wrote in message ... Right now my VBA code creates a number of worksheets and then sets a bunch of their PageSetup properties. Because PageSetup can be so slow, I would like to set all of the properties at once. I tried: With Sheets(Array("Sheet2", "Sheet1")).PageSetup .LeftHeader = "foo" End With But I get a run-time error on the With statement (438 Object doesn't support this property or method). Is there some other way to accomplish this or to dynamically (and temporarily) define what the default PageSetup properties should be for newly added worksheets? TIA, josh |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting PageSetup Properties in Multiple Worksheets
Dave,
Basically that's what I have now (actually I do the PageSetup after I add and format each worksheet). I'm trying to avoid the time it takes to set a number of PageSetup properties multiplied by the number of worksheets I need to set them for. Thanks. josh "Dave Peterson" wrote in message ... How about just looping through the worksheets... dim wks as worksheet for each wks in worksheets(array("sheet1","sheet2")) with wks.pagesetup ... end with next wks Josh Sale wrote: Right now my VBA code creates a number of worksheets and then sets a bunch of their PageSetup properties. Because PageSetup can be so slow, I would like to set all of the properties at once. I tried: With Sheets(Array("Sheet2", "Sheet1")).PageSetup .LeftHeader = "foo" ... End With But I get a run-time error on the With statement (438 Object doesn't support this property or method). Is there some other way to accomplish this or to dynamically (and temporarily) define what the default PageSetup properties should be for newly added worksheets? TIA, josh -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting PageSetup Properties in Multiple Worksheets
Tom,
I considered this approach but couldn't quite figure out how to make it work. As I mentioned in my original post, I'm only looking to temporarily make these PageSetup properties the default for new worksheets. So I guess I would have to my code: - Find any existing Sheet.xlt, - Make a copy of this file, - Create a new single worksheet workbook with the appropriate PageSetup properties set, - Save it as Sheet.xlt in the appropriate place (potentially overwriting the one that's there), - Do all of my application processing, and then finally - Delete the Sheet.xlt I created and put back the original Sheet.xlt assuming there was one. Is this what you're purposing? Kind of a lot of work but it might be worth it. I'm not even certain it would work. I worry that Excel might open and process Book.xlt and Sheet.xlt as part of its basic initialization and therefore wouldn't even "see" my change to Sheet.xlt during the active Excel session. Is this what you were thinking or am I off in the weeds. Thanks again, josh "Tom Ogilvy" wrote in message ... Set it for one sheet, then copy that sheet instead of adding sheets. Also lookin xl help for templates customizing workbook defaults also Create a sheet template for new worksheets See Sheet.xlt If you create a single sheet workbook and name it sheet.xlt (after you format it the way you want), then save it in the xlStart directory, then insert = WorkSheet -- Regards, Tom Ogilvy "Josh Sale" <jsale@tril dot cod wrote in message ... Right now my VBA code creates a number of worksheets and then sets a bunch of their PageSetup properties. Because PageSetup can be so slow, I would like to set all of the properties at once. I tried: With Sheets(Array("Sheet2", "Sheet1")).PageSetup .LeftHeader = "foo" ... End With But I get a run-time error on the With statement (438 Object doesn't support this property or method). Is there some other way to accomplish this or to dynamically (and temporarily) define what the default PageSetup properties should be for newly added worksheets? TIA, josh |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting PageSetup Properties in Multiple Worksheets
You may want to try the suggestion in Tom's post from keepitcool.
Josh Sale wrote: Dave, Basically that's what I have now (actually I do the PageSetup after I add and format each worksheet). I'm trying to avoid the time it takes to set a number of PageSetup properties multiplied by the number of worksheets I need to set them for. Thanks. josh "Dave Peterson" wrote in message ... How about just looping through the worksheets... dim wks as worksheet for each wks in worksheets(array("sheet1","sheet2")) with wks.pagesetup ... end with next wks Josh Sale wrote: Right now my VBA code creates a number of worksheets and then sets a bunch of their PageSetup properties. Because PageSetup can be so slow, I would like to set all of the properties at once. I tried: With Sheets(Array("Sheet2", "Sheet1")).PageSetup .LeftHeader = "foo" ... End With But I get a run-time error on the With statement (438 Object doesn't support this property or method). Is there some other way to accomplish this or to dynamically (and temporarily) define what the default PageSetup properties should be for newly added worksheets? TIA, josh -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting PageSetup Properties in Multiple Worksheets
This is pretty cool ... and it even seems to work!
Is there anyway to keep the File | Page Setup dialog flashing on the screen? Thanks! josh "Tom Ogilvy" wrote in message ... for Code, KeepItCool post this method. Format a sheet. Group all the sheets making it the first sheet in the group. The use sendkeys: ' sheet3 is the pre-formatted sheet sheets(array("sheet2","sheet3","sheet4")).select sheets("sheet3").activate SendKeys "{enter}" Application.Dialogs(xlDialogPageSetup).Show keepITcool As I recall, this doesn't do every setting, but most of the more common ones. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Set it for one sheet, then copy that sheet instead of adding sheets. Also lookin xl help for templates customizing workbook defaults also Create a sheet template for new worksheets See Sheet.xlt If you create a single sheet workbook and name it sheet.xlt (after you format it the way you want), then save it in the xlStart directory, then insert = WorkSheet -- Regards, Tom Ogilvy "Josh Sale" <jsale@tril dot cod wrote in message ... Right now my VBA code creates a number of worksheets and then sets a bunch of their PageSetup properties. Because PageSetup can be so slow, I would like to set all of the properties at once. I tried: With Sheets(Array("Sheet2", "Sheet1")).PageSetup .LeftHeader = "foo" ... End With But I get a run-time error on the With statement (438 Object doesn't support this property or method). Is there some other way to accomplish this or to dynamically (and temporarily) define what the default PageSetup properties should be for newly added worksheets? TIA, josh |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting PageSetup Properties in Multiple Worksheets
I wasn't thinking anything. I was telling you about book.xlt and sheet.xlt.
However, it doesn't read them just one time. Somehow your list of activities doesn't seem as long as setting up the formats on every sheet in the workbook. -- Regards, Tom Ogilvy "Josh Sale" <jsale@tril dot cod wrote in message ... Tom, I considered this approach but couldn't quite figure out how to make it work. As I mentioned in my original post, I'm only looking to temporarily make these PageSetup properties the default for new worksheets. So I guess I would have to my code: - Find any existing Sheet.xlt, - Make a copy of this file, - Create a new single worksheet workbook with the appropriate PageSetup properties set, - Save it as Sheet.xlt in the appropriate place (potentially overwriting the one that's there), - Do all of my application processing, and then finally - Delete the Sheet.xlt I created and put back the original Sheet.xlt assuming there was one. Is this what you're purposing? Kind of a lot of work but it might be worth it. I'm not even certain it would work. I worry that Excel might open and process Book.xlt and Sheet.xlt as part of its basic initialization and therefore wouldn't even "see" my change to Sheet.xlt during the active Excel session. Is this what you were thinking or am I off in the weeds. Thanks again, josh "Tom Ogilvy" wrote in message ... Set it for one sheet, then copy that sheet instead of adding sheets. Also lookin xl help for templates customizing workbook defaults also Create a sheet template for new worksheets See Sheet.xlt If you create a single sheet workbook and name it sheet.xlt (after you format it the way you want), then save it in the xlStart directory, then insert = WorkSheet -- Regards, Tom Ogilvy "Josh Sale" <jsale@tril dot cod wrote in message ... Right now my VBA code creates a number of worksheets and then sets a bunch of their PageSetup properties. Because PageSetup can be so slow, I would like to set all of the properties at once. I tried: With Sheets(Array("Sheet2", "Sheet1")).PageSetup .LeftHeader = "foo" ... End With But I get a run-time error on the With statement (438 Object doesn't support this property or method). Is there some other way to accomplish this or to dynamically (and temporarily) define what the default PageSetup properties should be for newly added worksheets? TIA, josh |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
recalculate page breaks after setting Zoom in pagesetup | Excel Discussion (Misc queries) | |||
Excel VBA: Setting PageSetup.TopMargin for _EACH_ page | Excel Programming | |||
Q: copying PageSetup properties from one worksheet to another | Excel Programming | |||
read ZOOM after setting FitToPagesWide in pagesetup | Excel Programming | |||
read ZOOM after setting FitToPagesWide in pagesetup | Excel Programming |