Macro to save sheet as PDF with unique name

Tom Allen

Board Regular
Joined
Sep 26, 2014
Messages
92
Hi I am using the following macro code to save a sheet as a PDF. However the name of the saved PDF comes out as the the worksheet name which is called Standard Template.

I was wondering if anyone knew how to edit the code so that the name comes out as the following: Invoice #(value of cell H5)

Cell H5 contains a unique invoice number so for example if cell H5 had the number 10 in it then the PDF would save as Invoice #10

Code:
Sub Create_PDF_Invoice()
Dim wsh As Worksheet, vWshs, vWshName

vWshs = Array("Standard Template")
With ActiveWorkbook
    For Each vWshName In vWshs
        .Worksheets(vWshName).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "D:\Finance\Invoices Sent\" & vWshName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Next vWshName
End With
End Sub

Any help would be appreciated.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Rich (BB code):
Sub Create_PDF_Invoice()
Dim wsh As Worksheet, vWshs, vWshName

vWshs = Array("Standard Template")
With ActiveWorkbook
    For Each vWshName In vWshs
        .Worksheets(vWshName).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "D:\Finance\Invoices Sent\" & vWshName.Range("H5").Value, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Next vWshName
End With
End Sub
 

Tom Allen

Board Regular
Joined
Sep 26, 2014
Messages
92
Thanks for the reply. Unfortunately the code seems to return Run-time error '424' Object required
 

Tom Allen

Board Regular
Joined
Sep 26, 2014
Messages
92

ADVERTISEMENT

Thank you that worked. Sorry I'm a bit of a novice at this but how would I edit the code again so that the name comes out as: Invoice #(value of H5)
Currently the name comes out as the value of H5 but it would be perfect if I could add a small bit of text before that value so the entire name would be something like Invoice #10 instead of just 10
 

Watch MrExcel Video

Forum statistics

Threads
1,108,806
Messages
5,524,983
Members
409,613
Latest member
Dalex100

This Week's Hot Topics

Top