Exporting Sheet to PDF (VBA)

omagoodness

New Member
Joined
Apr 17, 2016
Messages
41
Hello
I have a sheet that calculates payroll deductions. I want to archive the data and reset the sheet for the next pay period. The following code generates the "Archived message" and then the error message but does not generate the pdf file. Also, the code snippet to clear the data also clears the header row. I've been looing at this for 2 days now and cannot see where the errors are. Can anyone help, please and thank you? Would attach sample file but I don't see the attachment option.

VBA Code:
Private Sub cmdArchivePay_Click()
'declare the variables
'***************************************************************

          
'Locate the data to Archive
Dim Awb As Workbook
    Set Awb = ActiveWorkbook
Dim Asht As Worksheet
    Set Asht = Awb.Sheets("Payroll Calculation")
Dim Myrng As Range
    Set Myrng = Asht.Range("A1,O27")

'Define the save location and name of the archived data
Dim strFile As String, Myfile As Variant
    strFile = ThisWorkbook.Path & "\Payroll Archives" & "\" & Myfile
    Myfile = "Period Ending " & PE & ".pdf"

      
'**************************************************************************
'Export the data, in pdf format, to the Payroll Archives

On Error GoTo errHandler

Application.ScreenUpdating = False
Application.DisplayAlerts = False


    
Myrng.ExportAsFixedFormat Type:=xlTypePDF, filename:=Myfile, Quality:=xlQualityStandard, _
IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False

MsgBox "Data has been archived", vbOKOnly, "Payroll Archives"

'********************************************************************************
'After data has been archived, reset the table                                  '__________________________________________________
                                                                                '|How can I not clear the header row of the table?|
ActiveWorkbook.Sheets("Payroll Calculation").Range("tblPayroll").Select         '|________________________________________________|
Cells.SpecialCells(xlCellTypeConstants).ClearContents
    

Application.DisplayAlerts = True
Application.ScreenUpdating = True

'***************************END OF EXPORT CODE ******************************************************

exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
    
End Sub
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,614
The PE variable is undeclared and undefined, so I don't know what it's meant to be or how you set its value. I've just set it to a string.

Try changing these lines, and put them in the order shown (Myfile should be defined before strFile).

VBA Code:
Dim PE As String

PE = "Sep 2020"
Myfile = "Period Ending " & PE & ".pdf"
strFile = ActiveWorkbook.Path & "\Payroll Archives\" & Myfile


Myrng.ExportAsFixedFormat Type:=xlTypePDF, filename:=strFile, Quality:=xlQualityStandard, _
IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False

MsgBox "Data has been archived to" & vbCrLf & strFile, vbOKOnly, "Payroll Archives"

The range name "tblPayroll" indicates a table, so to clear the table's data rows, leaving the header row intact, try:
VBA Code:
    ActiveWorkbook.Sheets("Payroll Calculation").ListObjects("tblPayroll").DataBodyRange.Clear
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,987
Members
414,115
Latest member
SFUser

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
Top