[RESOLVED] Macro to add today's date when saving the file in a different format

[VbtoMe]

Hello!

I want to send specific worksheets in Excel to PDF and save in a specified folder. I have done this successfully using:

Sub SendPDF()

' SendToPDF Macro

    Sheets(Array("COVER", "In DAILY Summary", "Out DAILY Summary", _
        "Out DAILY by Time")).Select
    Sheets("COVER").Activate
    ChDir "C:\MyFileLocation"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:/MyOutputLocation\Stats.pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
End Sub

However, I'd like to take It one step further by adding today's date when it saves into the file name. So the above would become "Stats 11-03-2016.pdf".

I found this in an inferior forum for adding the date part. I've tried using part of the below in the above but doesn't work. Any ideas please?

Sub Save()

Dim dtDate As Date
dtDate = Date

Dim strFile As String
strFile = "X:\file" & Format(dtDate, "mm-dd-yyyy") & ".xlsm"

ActiveWorkbook.SaveAs Filename:=strFile, FileFormat _
    :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

End Sub

Thanks!

[westconn1]

try like

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:/MyOutputLocation\Stats" & format(Date,"dd-mm-yyyy") & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

though i am not sure you should have a / in your file path

[VbtoMe]

The / was my typo when I was changing the file path to something non-identifying I'll give this a go now.

Thanks.

[VbtoMe]

I used this but error 1004, Select method of sheets class failed.

Sub SendPDF()

' Send To PDF with Date Macro

    Sheets(Array("COVER", "In DAILY Summary", "Out DAILY Summary", _
        "Out DAILY by Time")).Select
    Sheets("COVER").Activate
    ChDir "C:\MyFileLocation"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\MyOutputLocation\Stats" & Format(Date, "dd-mm-yyyy") & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
End Sub

[westconn1]

the error would appear to be for the first line of code?

the first 3 lines are possibly irrelevant to the task, try

Sub SendPDF()

    sheets("cover").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\MyOutputLocation\Stats" & Format(Date, "dd-mm-yyyy") & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
End Sub

[VbtoMe]

the error would appear to be for the first line of code?

the first 3 lines are possibly irrelevant to the task, try

Sub SendPDF()

    sheets("cover").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\MyOutputLocation\Stats" & Format(Date, "dd-mm-yyyy") & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
End Sub

I need the first few lines because I have to nominate which worksheets I want printing or it prints all (there are 7 worksheets, I only want to export 4 to PDF).

[VbtoMe]

I need the first few lines because I have to nominate which worksheets I want printing or it prints all (there are 7 worksheets, I only want to export 4 to PDF).

Managed to work round my issue. I added

"worksheets("cover".visible = True" etc.

for the worksheets I wanted to print and used the same code as before and it works.