+ Reply to Thread
Results 1 to 11 of 11

Setting PageSetup Properties in Multiple Worksheets

  1. #1
    Josh Sale
    Guest

    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. #2
    Jim Cone
    Guest

    Re: 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 news:uBEUT%23$OGHA.3936@TK2MSFTNGP12.phx.gbl...
    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. #3
    Dave Peterson
    Guest

    Re: 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. #4
    Tom Ogilvy
    Guest

    Re: 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
    news:uBEUT%23$OGHA.3936@TK2MSFTNGP12.phx.gbl...
    > 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. #5
    Tom Ogilvy
    Guest

    Re: 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" <twogilvy@msn.com> wrote in message
    news:uu2UftAPGHA.3460@TK2MSFTNGP15.phx.gbl...
    > 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
    > news:uBEUT%23$OGHA.3936@TK2MSFTNGP12.phx.gbl...
    > > 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. #6
    Josh Sale
    Guest

    Re: 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" <jim.coneXXX@rcn.comXXX> wrote in message
    news:OH3pijAPGHA.2320@TK2MSFTNGP11.phx.gbl...
    > 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
    > news:uBEUT%23$OGHA.3936@TK2MSFTNGP12.phx.gbl...
    > 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. #7
    Josh Sale
    Guest

    Re: 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" <petersod@verizonXSPAM.net> wrote in message
    news:4403AC5D.79FEE238@verizonXSPAM.net...
    > 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. #8
    Josh Sale
    Guest

    Re: 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" <twogilvy@msn.com> wrote in message
    news:uu2UftAPGHA.3460@TK2MSFTNGP15.phx.gbl...
    > 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
    > news:uBEUT%23$OGHA.3936@TK2MSFTNGP12.phx.gbl...
    >> 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. #9
    Dave Peterson
    Guest

    Re: 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" <petersod@verizonXSPAM.net> wrote in message
    > news:4403AC5D.79FEE238@verizonXSPAM.net...
    > > 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. #10
    Josh Sale
    Guest

    Re: 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" <twogilvy@msn.com> wrote in message
    news:%2351xuVBPGHA.740@TK2MSFTNGP12.phx.gbl...
    > 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" <twogilvy@msn.com> wrote in message
    > news:uu2UftAPGHA.3460@TK2MSFTNGP15.phx.gbl...
    >> 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
    >> news:uBEUT%23$OGHA.3936@TK2MSFTNGP12.phx.gbl...
    >> > 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. #11
    Tom Ogilvy
    Guest

    Re: 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
    news:ujSQ49HPGHA.2888@tk2msftngp13.phx.gbl...
    > 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" <twogilvy@msn.com> wrote in message
    > news:uu2UftAPGHA.3460@TK2MSFTNGP15.phx.gbl...
    > > 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
    > > news:uBEUT%23$OGHA.3936@TK2MSFTNGP12.phx.gbl...
    > >> 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1