CODEWITHSUNDEEP
excelsaveruntime-errorvba
user1610138
user1610138

Reputation: 21

Runtime error 1004 Document not saved when try to save file with date in filename

So I have a macro which work perfectly fine as it is below. It loops through a data validation drop-down and saves a pdf for each country in dropdown. However when I try to edit the macro so that the filename includes the date in addition to the country name (D14) I encounter the runtime error 1004 Document could not be saved. I am very new to VBA so I have no idea how to solve this...I would really really appreciate some help

Steph

Sub Create_PDFs()
'
' Create_PDFS Macro
'
' Keyboard Shortcut: Ctrl+y
'
Const sheetToExportName = "Graphs"
Const sheetWithCountryList = "Master Sheet"
Const CountryListAddress = "AQ6:AQ38"
Const chosenCountryCell = "D14"
Const sheetWithChosenCell = "Graphs"

Dim CountryList As Range
Dim anyCountry As Range

Set CountryList = _
ThisWorkbook.Worksheets(sheetWithCountryList). _
Range(CountryListAddress)
For Each anyCountry In CountryList
ThisWorkbook.Worksheets(sheetWithChosenCell). _
Range(chosenCountryCell) = anyCountry
ThisWorkbook.Worksheets(sheetToExportName).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "N:\International Finance Division\RAT Advanced Economies - Chartpacks\Country Risks\Created PDFs\" & ActiveSheet.Range("D14").Value & " - Country Risk Indicators.pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
        Next
        Set CountryList = Nothing
End Sub

Upvotes: 2

Views: 6920

Answers (1)

Daniel
Daniel

Reputation: 13122

Clean the value of your date of special characters.

Assuming that range will always be a date, replace:

ActiveSheet.Range("D14").Value

with something like this:

format(ActiveSheet.Range("D14").Value,"YYYYMMDD")

Feel free to use a different format than "YYYYMMDD", but make sure you do not use "/" as indicated by shahkalpesh's comment.

Upvotes: 1

Related Questions