Next invoice number save as PDF/Excel - Make excel file export only print area or range

[hoonose]

Hello

I've used the very helpful podcast episode 1505 (FAQ #3) to create an Excel file with next invoice number feature. I'm very happy with the results but would like to change two parts of the supplied code.

1. Make the saved excel file .xlsx only export just the print area or a range of cells including the formatting.
2. Is it possible to have the clear contents element of the code ask if I want to clear or not?

This is the code I used from Next Invoice Number post, the only changes I made were to folder names and cell locations.

Sub SaveInvoiceBothWaysAndClear()
    Dim NewFN As Variant
    ' Create the PDF First
    NewFN = "C:\aaa\PDFInvoices\Inv" & Range("E5").Value & ".pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewFN, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
    ' Next, Save the Excel File
    ActiveSheet.Copy
    NewFN = "C:\aaa\Inv" & Range("E5").Value & ".xlsx"
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    ' Increment the invoice number
    Range("E5").Value = Range("E5").Value + 1
    ' Clear out the invoice fields
    Range("A20:E39").ClearContents
End Sub

I've messed about for hours trying to sort this but to no avail. Would appreciate any assistance on this matter. Thank you

 

[John_w]

Incorporate these changes:

    'Save A20:E39 to .xlsx file
    Dim saveRange As Range, newWorkbook As Workbook
    NewFN = "C:\aaa\Inv" & Range("E5").Value & ".xlsx"
    Set saveRange = ActiveSheet.Range("A20:E39")
    Set newWorkbook = Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
    saveRange.Copy newWorkbook.Worksheets(1).Range("A1")
    newWorkbook.SaveAs NewFN, xlOpenXMLWorkbook
    newWorkbook.Close False

    ' Clear out the invoice fields?
    If MsgBox("Clear invoice fields?", vbQuestion + vbYesNo, "Save Invoice") = vbYes Then
        Range("A20:E39").ClearContents
    End If