Macro to save to PDF with date shown in cell G1 as part of file name

[C with no eyes]

Hi,

I have seen many other threads in relation to this however I cannot seem to get it to work. I'm sorry for repeating this question but I've tried so many variations and have given up.

This code does work:


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"S:\Month & Year End Documents\Sales Ledger Month End\Daily turnover\Daily turnover.pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True


But I want the file name to be "Daily turnover [date shown in cell g1] and cell g1 is actually a merged cell spanning g1:h2

So far I have this but it highlights yellow and requests debugging:


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"S:\Month & Year End Documents\Sales Ledger Month End\Daily turnover\Daily turnover " & Range("G1").Value & ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True


Do I need to format the date? Or is it because g1 is a merged cell?

Can anyone help me, please? I am using Excel 2010

 

[John_w]

If the default Excel date format contains / or \ characters then those are invalid characters in a file name and you must use a different date format in the code.

This saves the active sheet with the G1 date part of the file name formatted as "yyyy-mm-dd".

Public Sub Save_Active_Sheet_As_PDF()

    Dim PDFfilename As String
    
    PDFfilename = "S:\Month & Year End Documents\Sales Ledger Month End\Daily turnover\Daily turnover " & Format(Range("G1").Value, "yyyy-mm-dd") & ".pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfilename, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    
End Sub

 

[C with no eyes]

Put the format around my date range and it now works perfectly.

Thank you very much