create PDF file from worksheet

[PEAKCAB]

Hello,

I would appreciate some advice with an issue i have from you experts out there. I have a macro to create the next invoice number and saves the file with the invoice number and creates the next invoice. What I would like to do is save the first page as a PDF File. I have this working but it produces all 5 pages - what do i have to do to print only the 1st page?

Sub SaveInvWithNewName()
Dim NewFN As Variant
PostToRegister
'MAKEPDF Macro'
ActiveSheet.ExportAsFixedFormat Type:=x1TypePDF, Filename:="Y:\Dropbox\Accounts\Accounts\Sales\DELIVERY NOTES\SI" & Range("J22")
'Copy Invoice to a new workbook
ActiveSheet.Copy
NewFN = "Y:\Dropbox\Accounts\Accounts\Sales\DELIVERY NOTES\DN" & Range("J22").Value & ".xlsx"

Application.DisplayAlerts = False 'Inhibit display/ default yes to application dialogs

With ActiveWorkbook 'New Invoice workbook
'Save as
.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
'Delete the Macro button shapes
.ActiveSheet.Shapes("5-Point Star 1").Cut
.ActiveSheet.Shapes("Rounded Rectangle 2").Cut
'Close and save
.Close SaveChanges:=True
End With

Application.DisplayAlerts = True 'Reset alerts

NextInvoice

End Sub

thanks for you help in advance

 

[dhubz]

Try this, I haven't tested it recently, but it was part of a macro I've used in the past. Modify your filelocation and Range accordingly, but this should only save your active sheet.

Sub Save_ActivePDF()

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\filelocation\" & Range("A1") & ".pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False
End Sub

You can add this at the top as this first line, if you want to specify sheets.

Sheets(Array("Sheet1", "Sheet2")).Select

 

[PEAKCAB]

Hi dhubs,

thanks. I have tried it and now gives me a syntax error. Am i missing something or put it in the wrong place?

Sub SaveInvWithNewName()
Dim NewFN As Variant
PostToRegister
Save_ActivePDF()

Sheets(Array("Sheet1")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"Y:\Dropbox\Accounts\Accounts\Sales\DELIVERY NOTES\SI" & Range("J22") & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False

'Copy Invoice to a new workbook
ActiveSheet.Copy
NewFN = "Y:\Dropbox\Accounts\Accounts\Sales\DELIVERY NOTES\DN" & Range("J22").Value & ".xlsx"

Application.DisplayAlerts = False 'Inhibit display/ default yes to application dialogs

With ActiveWorkbook 'New Invoice workbook
'Save as
.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
'Delete the Macro button shapes
.ActiveSheet.Shapes("5-Point Star 1").Cut
.ActiveSheet.Shapes("Rounded Rectangle 2").Cut
'Close and save
.Close SaveChanges:=True
End With

Application.DisplayAlerts = True 'Reset alerts

NextInvoice

End Sub

 

[dhubz]

Looks like you're missing a "\" at the end of your path.
"Y:\Dropbox\Accounts\Accounts\Sales\DELIVERY NOTES\SI"

I tested it on my end as I pasted, seems ok here.

 

[dhubz]

Delete this too "Save_ActivePDF()"

 

[PEAKCAB]

Looks like you're missing a "\" at the end of your path.
"Y:\Dropbox\Accounts\Accounts\Sales\DELIVERY NOTES\SI"

I tested it on my end as I pasted, seems ok here.

Hi
the SI" is the prefix of the file name that is to be saved

I have deleted the text but now have an issue with Sheets(Array - my error is subscript out of range

if i only want to print part of sheet 1, i.e. the first A4 page of 5 would i have enter further code - sorry for sounding so dim but i am new to this

 

[dhubz]

Don't worry about it, I'm pretty new to this too. I thought SI was your folder. I would pull it out as part of the name build
"Y:\Dropbox\Accounts\Accounts\Sales\DELIVERY NOTES\" & "SI" & Range("J22") & ".pdf"
Your going to have to set a range you can try this to force sheet1 range- "sheet1" needs to match your tab name

Change ActiveSheet to Sheets("Sheet1").Range("B2:H28")

This will force the PDF as Sheet 1 from B2 to H28. Set your parameters accordingly

 

[PEAKCAB]

Don't worry about it, I'm pretty new to this too. I thought SI was your folder. I would pull it out as part of the name build
"Y:\Dropbox\Accounts\Accounts\Sales\DELIVERY NOTES\" & "SI" & Range("J22") & ".pdf"
Your going to have to set a range you can try this to force sheet1 range- "sheet1" needs to match your tab name

Change ActiveSheet to Sheets("Sheet1").Range("B2:H28")

This will force the PDF as Sheet 1 from B2 to H28. Set your parameters accordingly

All sorted, thank you very much for your time and patience - very much appreciated