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

hoonose

New Member
Joined
Apr 20, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.

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

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,809
Incorporate these changes:
VBA Code:
    '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
VBA Code:
    ' Clear out the invoice fields?
    If MsgBox("Clear invoice fields?", vbQuestion + vbYesNo, "Save Invoice") = vbYes Then
        Range("A20:E39").ClearContents
    End If
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,132,644
Messages
5,654,552
Members
418,140
Latest member
ahepple86

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