Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    May 2015
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exclamation Issue saving PageSetup formats to PDF file

    Hoping someone can assist. I want to select all worksheets within my workbook and save worksheets into 1 PDF file. I have found some VBA code online (see below) which does this, but unfortunately the PDF file does not have the Page Setups I had previously applied within each worksheet. Each worksheet has Page Setup to have orientation of Landscape and FitToPagesWide / FitToPagesTall to = 1.

    Any idea why PageSetups don't come across when saving to PDF ? Any help is greatly appreciated.



    Code:
    Private Sub Save_File_to_PDF()
    '---- Create a dynamic array which will house all the worksheet names in the workbook to be saved to PDF file
        Dim myArray() As Variant
        Dim i As Integer
        For i = 1 To Sheets.Count
            ReDim Preserve myArray(i - 1)
            myArray(i - 1) = i
        Next i
        
    '--- select all the worksheets within the array so can save to 1 PDF file
        Sheets(myArray).Select    '---- this command seems to reset the PageSetting back to the defaults for some reason
    
       '--- save file ---
         ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
         Filename:="C:\Assets.PDF" , _
         Quality:=xlQualityStandard, _
         IncludeDocProperties:=False, _
         IgnorePrintAreas:=True, _
         OpenAfterPublish:=True
         
    End Sub

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    cirugio,

    Welcome to the Lounge as a new poster!

    I tried this and it worked fine.

    Code:
    Sub Macro1()
        ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "G:\BEKDocs\Excel\Finances\Phone Bills\Phone Bill Analysis with Data Plan 201501.pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, OpenAfterPublish:=True
    End Sub
    Printed all 17 pages (sheets) of this my workbook.
    Just set the Print Settings ahead of time since this doesn't change them and of course substitute your file name.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    May 2015
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for your feedback, but I still can't seem to get this to go. I have a routine that I run before hand (see below) which formats each worksheet within the workbook to have my desired PageSetups (landscape and fit to 1 page), but when I call the macro you provided it seems to loose my PageSetups for some reason?? This confuses me. Thoughts???


    Code:
    Private Sub Format_Worksheets_Fit1Page()
    
     strTargetWorkbook = "ASSET.xlsm"   '$$1
     Dim sh As Worksheet, wb As Workbook
     Set wb = Workbooks(strTargetWorkbook)
     
     For Each sh In Workbooks(strTargetWorkbook).Worksheets
     
    
      '--- PageSetup to fit to 1 page & make Landscape ---
    
     Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .LeftMargin = Application.InchesToPoints(0.7)
            .RightMargin = Application.InchesToPoints(0.7)
            .TopMargin = Application.InchesToPoints(0.75)
            .BottomMargin = Application.InchesToPoints(0.75)
            .HeaderMargin = Application.InchesToPoints(0.3)
            .FooterMargin = Application.InchesToPoints(0.3)
            .PrintComments = xlPrintNoComments
            .Orientation = xlLandscape
            .PaperSize = xlPaperLetter
            .FirstPageNumber = xlAutomatic
            .Order = xlDownThenOver
            .FitToPagesWide = 1
            .FitToPagesTall = 1
        End With
        Application.PrintCommunication = True
        
      Next sh
      
    CALL Save_File_to_PDF
      
    End Sub 
    
    
    Private Sub Save_File_to_PDF ()
        ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\ASSET.pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, OpenAfterPublish:=True
    End Sub
    Last edited by cirugio; 2015-05-29 at 16:57.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    cirugio,

    If you want your code to set the print settings:

    Code:
    Option Explicit
    
    Sub Macro2()
    '
    ' Macro2 Macro
    '
    
    '
       ActiveWorkbook.Sheets.Select
       
        Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .LeftHeader = ""
            .CenterHeader = ""
            .RightHeader = ""
            .LeftFooter = ""
            .CenterFooter = ""
            .RightFooter = ""
            .LeftMargin = Application.InchesToPoints(0.7)
            .RightMargin = Application.InchesToPoints(0.7)
            .TopMargin = Application.InchesToPoints(0.75)
            .BottomMargin = Application.InchesToPoints(0.75)
            .HeaderMargin = Application.InchesToPoints(0.3)
            .FooterMargin = Application.InchesToPoints(0.3)
            .PrintHeadings = False
            .PrintGridlines = False
            .PrintComments = xlPrintNoComments
            .CenterHorizontally = False
            .CenterVertically = False
            .Orientation = xlLandscape
            .Draft = False
            .PaperSize = xlPaperLetter
            .FirstPageNumber = xlAutomatic
            .Order = xlDownThenOver
            .BlackAndWhite = False
            .Zoom = False
            .FitToPagesWide = 1           '<---- Sets the 1 page wide feature
            .FitToPagesTall = 1           '<---- Sets the 1 page tall feature
            .PrintErrors = xlPrintErrorsDisplayed
            .OddAndEvenPagesHeaderFooter = False
            .DifferentFirstPageHeaderFooter = False
            .ScaleWithDocHeaderFooter = True
            .AlignMarginsHeaderFooter = True
            .EvenPage.LeftHeader.Text = ""
            .EvenPage.CenterHeader.Text = ""
            .EvenPage.RightHeader.Text = ""
            .EvenPage.LeftFooter.Text = ""
            .EvenPage.CenterFooter.Text = ""
            .EvenPage.RightFooter.Text = ""
            .FirstPage.LeftHeader.Text = ""
            .FirstPage.CenterHeader.Text = ""
            .FirstPage.RightHeader.Text = ""
            .FirstPage.LeftFooter.Text = ""
            .FirstPage.CenterFooter.Text = ""
            .FirstPage.RightFooter.Text = ""
        End With
        Application.PrintCommunication = True
        
        ActiveWorkbook.Sheets(1).Select
        
    End Sub
    Just change as appropriate.

    Note: With the code posted in my first post the sheets do NOT have to be selected to be printed.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    New Lounger
    Join Date
    May 2015
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    that worked beautifully!!!! Thank you so much.

Posting Permissions

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