Macro to Save a PDF in folder by month

Hi all,

Thanks in advance for your time and apologies if there is a similar post somewhere with in the forum. I've searched around but can't seem to find what I'm looking for.

I was to save my worksheet as a PDF in a folder based on the month in a specific cell. I've previously acheived this but can't for the life of me figure out how I managed it. The worksheet got corrupted and I had to start again.

Here is my current code:

Sub SaveInvAsPDF()
    Dim NewFN As Variant
    ' Copy Invoice to a new workbook
    NewFN = "D:\...\inv" & Range("G15").Value & " - " & Range("A14").Value & ".pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=NewFN, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True
    NextInvoice
End Sub

Basically, I want it to save in a folder based on a month in a date cell (G13) with the actual file name being "inv 'G15' - 'A14'.pdf" The save name bit currently works but I can't seem to get it to save in the specific folder, which are already generated, if that makes sense.

I've tried something similar as

 Dim GetMonth As Variant
GetMonth = Range("G13").Value Format(Date,"mmm")

and inserted it as

 NewFN = "D:\...\inv\" & GetMonth & "\" & Range("G15").Value & " - " & Range("A14").Value & ".pdf"

but it saves to a different location with the file name as either "False.pdf" or "invFALSE.pdf"

Any help is much appriciated.

Cheers

Tom

Hi, Tom,

maybe like this

Sub SaveInvAsPDF()
Dim NewFN As String
' Copy Invoice to a new workbook
NewFN = "D:\...\inv\" & Format(Range("G13").Value, "mmm") & "\" & Range("G15").Value & " - " & Range("A14").Value & ".pdf"
ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, Filename:=NewFN, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=False
NextInvoice
End Sub

Ciao,
Holger

Hi, Tom,

1004 is the worst documented error in Excel VBA to my taste - it could mean about anything, and definitely nothing in specific.

How does the date in Range("G13") look like - that could be one issue. Is the name of the folder Apr if it´s today´s date in G13? Do you have rights to write to the folder? Trusted Location? I altered the path for my computer and the file was saved like I expected it to be:

Sub SaveInvAsPDF_HaHoBe()
Dim NewFN As String
' Copy Invoice to a new workbook
NewFN = "C:\HaHoBe\inv\" & Format(Range("G13").Value, "mmm") & "\" & Range("G15").Value & " - " & Range("A14").Value & ".pdf"
'NewFN = "D:\...\inv\" & Format(Range("G13").Value, "mmm") & "\" & Range("G15").Value & " - " & Range("A14").Value & ".pdf"
ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, Filename:=NewFN, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=False
'NextInvoice
End Sub

Ciao,
Holger