Macro button report functionality

[JToulson91]

Hi all,

I have a workbook that is to be sent out as a sales tool - the user inputs data and the workbook figures out estimated energy consumption - all this is working great and today I've been trying to add a pdf-export function.
I have created a worksheet that is formatted so that it will be on two pages and not be all over the place. I've obtained and modified some VBA code (below) that opens a 'save-as' dialog box in pdf format - this code works when I'm on the 'Report sheet' and run the code through the Microsoft VBA window (ie hit F5 when on the code).

I've also added a Format control button on the main sheet where the user inputs data and sees the results in a more monitor-friendly format. The same VBA code is assigned to the button but, when pressed, the save-as box appears, the pdf is published but it has changed the printing margins//my selected range or something, which results in about 1/3rd of the first page of my report missing and none of the 2nd page appears at all!!

Sub PrintPDFAll()'turn off screen updating
Dim Opendialog
Dim MyRange As Range
    Application.ScreenUpdating = False
    'open dialog and set file type
    Opendialog = Application.GetSaveAsFilename("", filefilter:="PDF Files (*.pdf), *.pdf", _
                                               Title:="Your BC")
    'if no value is added for file name
    If Opendialog = False Then
        MsgBox "The operation was not successful"
        Exit Sub
    End If
    'set the named range for the PDF
    With Sheet8
        .Range("A1:I" & Cells(Rows.Count, "A").End(xlUp).Row).Name = "PDFRng"
    End With
    'set range
    Set MyRange = Sheet8.Range("PDFRng")
    Sheet8.PageSetup.PrintArea = "PDFRng"
    'create the PDF
    On Error Resume Next
    MyRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Opendialog, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=True
    'error handler
    On Error GoTo 0
End Sub

Any advice would be gratefully received!!

 

[JToulson91]

I've managed to fix this using the code below. I've removed the "With" function and instead defined the range of cells directly rather than through naming a range.

Sub PrintPDFAll()'turn off screen updating
Dim Opendialog
Dim MyRange As Range
    Application.ScreenUpdating = False
    'open dialog and set file type
    Opendialog = Application.GetSaveAsFilename("", filefilter:="PDF Files (*.pdf), *.pdf", _
                                               Title:="Your BC")
    'if no value is added for file name
    If Opendialog = False Then
        MsgBox "The operation was not successful"
        Exit Sub
    End If
    'set range
    Set MyRange = Sheet8.Range("PDFReport")
    Sheet8.PageSetup.PrintArea = "PDFReport"
    'create the PDF
    On Error Resume Next
    MyRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Opendialog, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    'error handler
    On Error GoTo 0
End Sub