create PDF file from worksheet

PEAKCAB

New Member
Joined
Jul 16, 2018
Messages
48
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

dhubz

New Member
Joined
Sep 10, 2014
Messages
37
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.

VBA Code:
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.
VBA Code:
Sheets(Array("Sheet1", "Sheet2")).Select
 
Last edited:

PEAKCAB

New Member
Joined
Jul 16, 2018
Messages
48
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

New Member
Joined
Sep 10, 2014
Messages
37
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

New Member
Joined
Sep 10, 2014
Messages
37

ADVERTISEMENT

Delete this too "Save_ActivePDF()"
 

PEAKCAB

New Member
Joined
Jul 16, 2018
Messages
48
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

New Member
Joined
Sep 10, 2014
Messages
37
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

New Member
Joined
Jul 16, 2018
Messages
48
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,482
Messages
5,636,591
Members
416,927
Latest member
BNM8V6

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