Page Setup & Print Settings Not Working In VBA ??

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Page Setup & Print Settings Not Working In VBA ??

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Oxford, Connecticut USA.
    Posts
    235
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hi Excel Experts,
    I have recorded a macro where I select 3 worksheets in a workbook by clicking on the first one and shift-clicking on the last one to select all three sheets. I then go into the page setup and set my various settings, landscape, fit to 1 page tall, fit to 1 page wide, footers, etc. However, when I run the macro, none of the settings take even though I am getting no error messages. I made sure my zoom setting was set to false so that the fit to 1 page tall/wide would take but still no positive effect. Will I have to select each sheet individually and configure the page/print settings or am I missing something else ??

    Thanks,

    Joe Dowski
    Woodbury, CT. USA.

  2. #2
    Board Regular Corticus's Avatar
    Join Date
    Apr 2002
    Location
    Sarasota, FL
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well I'm not an Excel expert but...

    I think this will help. Select each of the sheets you need to print. Select View|Page Break Preview. In page break preview set your page breaks like you want the page to look, then use code like:
    Sub printsheet()
    Sheets("Sheet1").PrintOut Copies:=1, Collate:=True
    Sheets("Sheet2").PrintOut Copies:=1, Collate:=True
    Sheets("Sheet3").PrintOut Copies:=1, Collate:=True
    End Sub

    to print the pages.

    You can 'force' page breaks like:
    Sub printarea()

    With Sheets("Sheet1")
    .PageSetup.printarea = "$A$1:$G$20"
    .PrintOut Copies:=1, Collate:=True
    End With

    End Sub


    edit:
    I wanted to add that 'page setup' is 'sheet sensitive', in that adjustments you make to page breaks, orientation, margins are saved with the page from which you selected 'page setup' If you save every page's page setup like you want, when you reopen the workbook, those settings will remain.

    HTH,
    Corticus

    [ This Message was edited by: Corticus on 2002-12-10 11:10 ]

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Oxford, Connecticut USA.
    Posts
    235
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Corticus,

    I understand your reply, and I have specified the print area in code as well. What I am acutally have the most trouble with is getting the "fit to 1 page tall" and "fit to 1 page wide settings". That fact that my headers and footers aren't taking either just seems to be another symptom of the same problem.
    Any thoughts??

    J.

  4. #4
    Board Regular Corticus's Avatar
    Join Date
    Apr 2002
    Location
    Sarasota, FL
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    What are the dimensions of the pages?

    Perhaps it is too big? I have never had your problem occur when I forced the print macro to set the print area. Really, if you can get 'print preview' to display the sheet right, and you use the first code I provided, it should print what you see in 'print preview'.

    You could send me a dataless copy and I'll look at it if you want:
    corticus@hotmail.com

    Its hard to recreate your dilemma without knowing what the sheet looks like, but I'll be around if you repost.

    HTH,
    Corticus

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com