Exporting Sheet to PDF (VBA)

[omagoodness]

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.

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

 

[John_w]

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).

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:

    ActiveWorkbook.Sheets("Payroll Calculation").ListObjects("tblPayroll").DataBodyRange.Clear