Trouble with macro saving worksheets as PDFs

[pencekj]

My macro is supposed to go through every worksheet in the workbook and save them as a PDF, but it will only work for 5 worksheets and then errors out (there are 107 total worksheets in the workbook). Any ideas what I'm doing wrong?


Sub Macro1()

Dim ws As Worksheet
Dim starting_ws As Worksheet
Set starting_ws = ActiveSheet

For Each ws In ThisWorkbook.Worksheets
ws.Activate

Dim saveLocation As String
saveLocation = "C:\Users\James Pencek\Documents\Invoices" & ActiveSheet.Name & " " & Format(Now(), "MMM. YY") & ".pdf"

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveLocation

Next

starting_ws.Activate

End Sub

 

[rpaulson]

i would guess that the sheet name has an invalid charater

 

[GWteB]

The following four character < " | > are legit sheet name characters, but not valid for use in a file name.
Replacing each of them with the underscore character might solve your issue.

Sub Macro1()

    Dim ws As Worksheet
    Dim starting_ws As Worksheet
    Set starting_ws = ActiveSheet

    For Each ws In ThisWorkbook.Worksheets
        ws.Activate

        Dim saveLocation As String
        saveLocation = "C:\Users\James Pencek\Documents\Invoices" & ActiveSheet.Name & " " & Format(Now(), "MMM. YY") & ".pdf"

        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ValidateFileName(saveLocation)

    Next

    starting_ws.Activate
End Sub

Public Function ValidateFileName(ByVal argFileName As String) As String

    Const UNWANTED As String = "<>"":/\|?*"

    Dim Result As String, i As Long
    Result = argFileName
    For i = 1 To Len(UNWANTED)
        Result = Replace(Result, Mid(UNWANTED, i, 1), "_")
    Next
    ValidateFileName = Result
End Function

 

[pencekj]

i would guess that the sheet name has an invalid charater

The weird thing is that it still only saves the first 5 even if I rearrange the order of the worksheets. Ones that would save won't when I move them to 6th and ones that would error will save when I move them within the first 5. Its so bizarre

 

[GWteB]

Could you provide us with the error code and description? It could tell us more about the possible cause.

 

[pencekj]

Could you provide us with the error code and description? It could tell us more about the possible cause.

Run-time error '5':

Invalid procedure call or argument

and then when I click debug, it highlights this part of code

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveLocation

 

[GWteB]

Try this

Sub pencekj()

    Dim ws              As Worksheet
    Dim saveLocation    As String

    For Each ws In ThisWorkbook.Worksheets

        saveLocation = "C:\Users\James Pencek\Documents\Invoices" & ws.Name & " " & Format(Now(), "MMM. YY") & ".pdf"
        ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ValidateFileName(saveLocation)
        DoEvents

    Next
End Sub

Public Function ValidateFileName(ByVal argFileName As String) As String

    Const UNWANTED As String = "<>"":/\|?*"

    Dim Result As String, i As Long
    Result = argFileName
    For i = 1 To Len(UNWANTED)
        Result = Replace(Result, Mid(UNWANTED, i, 1), "_")
    Next
    ValidateFileName = Result
End Function