Macro button report functionality

JToulson91

New Member
Joined
Jul 20, 2015
Messages
8
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!!

Code:
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!!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top