Run Page setup in Excel and close setup window

[jazminecat23]

Hi all --

I have an Excel Spreadsheet that creates a bunch of other spreadsheets and saves them off every month. This all happens with a button click. It works fabulously, thanks in large part to the help I've gotten here. I now have requests from end users to setup the document print specifications. Note: Because the spreadsheets the users receive are generated from the master, and not just copies of the master, they don't have the same page setup parameters that the master has.

So I need to access the page setup for each one as it's created, and then move on to the next part step in the code. I used the macro recorder to record the code below, step 7a. I included the code before and after this bit so you can see what's happening - the other steps work perfectly - it's only 7a that I'm trying to make happen. Right now, what happens, is it does open the page setup window....and then it sits there waiting for me to close it. Because this master spreadsheet is used to create over 250 spreadsheets every month, having to close that window is really counterproductive - so I need it to close the window and continue on with the next step in the code. Anyone got any insight here?

[green] this works just perfectly [/green]
'7. Replace formulas with values in new workbook

    For Each ws In wbNew.Worksheets
      ws.Cells.Copy
      ws.Cells.PasteSpecial xlPasteValues
      ws.Activate
      ws.Cells(1, 1).Select
    Next
 [green]this is what I am trying to make work [/green] 
[red]  
 'New 6/27/07
 '7a. Setup sheet to print landscape 1 page wide, unspecified pages long
 
   With wbNew
      .Sheets("BudgetStatus").PageSetup
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With
    ActiveWindow.SelectedSheets.PrintPreview

[/red]
    
[green]this also works fine - when I don't have the above code in the module[/green]    
'8. save and close new workbook
        
    With wbNew
      .Sheets("BudgetStatus").Select
      .SaveAs "I:\SpreadsheetServer\Month End Reports" & "\" & sName & ".xls"
      .Close
    End With
 
Next

So what happens in the above code is it gets to the end of step 7a, and just sits there waiting for me to close the print preview window (even though I didn't stop recording the macro until I'd done that). Once I click close, it moves on to step 8 and continues with the rest of the code just fine.

Thanks again for all your help!

 

[PHV]

Replace this:
With wbNew
.Sheets("BudgetStatus").PageSetup
with this:
With wbNew.Sheets("BudgetStatus").PageSetup

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jazminecat23]

Hi PHV -

I tried that just now, and it still just opens the print preview window, and doesn't execute any of the code following that. It appears to only be getting to the first line of this page setup code before stopping, as it still appears onscreen in portrait orientation. Any other thoughts? Thanks much!

 

[PHV]

Is BudgetStatus the only sheet selected at the time of the page setup ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jazminecat23]

Hi -

Well, I want it to setup all of the sheets this way, but right now, yes, it's just selecting the budget status sheet. The problem is it opens the print preview window, and then doesn't do anything after that, and then it doesn't close the print preview window. I'd be happy if I could just get it to format the last sheet - called JE Detail - as the rest aren't printed as often.

Is there a way to set the page setup without opening the print preview window? That would work too. Just as long as I can set the parameters for how that last page is setup, I'm happy.

 

[PHV]

What happens if you comment out the PrintPreview line ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jazminecat23]

I think I answered my own question. I tried recording the macro while going to File -->Page Setup, instead of clicking on the page setup icon on the toolbar. This gave me the following code, which seems to be working as I wanted it to:

 With wbNew.Sheets("JE Detail").Select
    With Sheets("JE Detail").PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Sheets("JE Detail").PageSetup.PrintArea = ""
    With Sheets("JE Detail").PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With
    Sheets("BudgetStatus").Select
End With

I just left in all of the formatting. It's lengthy, but no one but me will ever see it, and it works so I don't wanna mess with it if I don't have to.