Add date to filename from cell value with VBA

[Chalieq]

Hi all,

I am new to using VBA and stumbled on my code. For work I need to create and save multiple sheets as pdf several times a month. As this is very time consuming I start using VBA to automate the process. At the moment I succeeded in creating and saving the sheets as pdf in a chosen folder. I want the pdf filename to be the value in sheet "Settl Dates" cell D6 (which is a date) and the ActiveSheet name. However somehow I am getting errors when using a date in the filename. For security reasons I have changed my path name, just now that my current path does work. Below my current code:

Private Sub CommandButton1_Click()
total_sheets = Worksheets.Count

For i = 12 To 17

Worksheets(i).Activate

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="\\PATHNAME\" & Worksheets("Settl Dates").Range("D6") & " " & ActiveSheet.Name & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

Next

End Sub

Your assistance will be highly appreciated, regards, Chalieq

[arlu1201]

Hello Chalieq,

Welcome to Excelforum. Be a part of large Excel community. Enjoy Learning.

[JeteMc]

Hello Chalieq and Welcome to Excel Forum.
You have posted a question in our introductions forum.
Please copy your post and use it to start a new thread in one of the Microsoft Office Application Help - Excel Help forum forums.
Let us know if you have any questions.