Need to add a filename to my VBA save

[Artisez]

I have a workbook that can have approx. 20 30 worksheets. I usually have to save each individually as a PDF. I have created a VBA to save all the sheets in a workbook as individual PDFs with each sheets name. But I am not great with VBA. I need to add the workbook name also. I would appreciate any help. Here is what I have:

>>> use code - tags <<<

Sub SaveEachWorksheetAsPdfFile()

Dim Worksheet As Worksheet

For Each Worksheet In Worksheets
Worksheet.ExportAsFixedFormat xlTypePDF, "C:\Art\" & filename & Worksheet.Name & ".pdf"
Next Worksheet

End Sub

 

[rollis13]

Try with this patch:

Option Explicit
Sub SaveEachWorksheetAsPdfFile()
    Dim Worksheet As Worksheet
    For Each Worksheet In Worksheets
        Worksheet.ExportAsFixedFormat xlTypePDF, "C:\Art\" & ThisWorkbook.Name & Worksheet.Name & ".pdf" '<- changed
    Next Worksheet
End Sub

 

[Artisez]

YOU ROCK!!! Works great Thank you!!!

 

[rollis13]

If you don't need the filetype in the name of the saved PDF you could use:

Worksheet.ExportAsFixedFormat xlTypePDF, "C:\Art\" & Replace(ThisWorkbook.Name, ".xlsm", "") & Worksheet.Name & ".pdf"

or even:

Worksheet.ExportAsFixedFormat xlTypePDF, "C:\Art\" & Replace(ThisWorkbook.Name, ".xlsm", "_") & Worksheet.Name & ".pdf"

Anyway, thanks for the positive feedback , glad having been of some help.

 

[Artisez]

This was working great, but I am getting a runtime error 5 for some reason. It did not initially get the error, but for some reason it does now, not sure why...?

Sub SaveEachWorksheetAsPdfFile()
    Dim Worksheet As Worksheet
    For Each Worksheet In Worksheets
        Worksheet.ExportAsFixedFormat xlTypePDF, "C:\Art\" & ThisWorkbook.Name & Worksheet.Name & ".pdf"
    Next Worksheet
End Sub

 

[rollis13]

If you haven't made any changes to your project, it's very strange.
So, just a guess: is the C:\Art\ path still available? In the meantime, have you hidden one or more sheets?

 

[Artisez]

No hidden sheets and the design is the same. What is strange is that it does save each sheet as a .pdf and once it has completed the error happens. Sorry I should have mentioned that earlier.

 

[rollis13]

The loop For/Next will never go over the last sheet. Please check when Debug is active: in your Immediate Window (Ctrl+G if not visible) type:
?Worksheets.Count
and press Enter and see if the count corresponds.

 

[Artisez]

Hmm, count was 19,

?Worksheets.Count
 19

I only have 18 sheets. I deleted an unneeded sheet; would that cause it?

 

[rollis13]

The loop is for all sheets since there is no code in the macro for restrictions.
As it is your macro will create 19 PDF even if one is unneeded. It will throw error '5' if this unneeded sheet is hidden.
To avoid looping through hidden sheets use:

If Not Worksheet.Visible = xlSheetHidden Then Worksheet.ExportAsFixedFormat xlTypePDF, "C:\Art\" & ThisWorkbook.Name & Worksheet.Name & ".pdf"

 

[Artisez]

I appreciate all your help! I keep getting the same error message. So, I went back to an earlier save, used the code you suggested and no errors. Ran the VBA a couple of times and received no errors. I even deleted a sheet, as well hid sheets and no errors.

Thanks, YOU ROCK!

 

[rollis13]

Also, just to avoid misunderstandings in VBE, it's best not to use variable names that are the same as keywords used in Excel macros. Instead of "Worksheet" use something like this:

Option Explicit
Sub SaveEachWorksheetAsPdfFile()
    Dim Wks As Worksheet
    For Each Wks In Worksheets
        If Not Wks.Visible = xlSheetHidden Then Wks.ExportAsFixedFormat xlTypePDF, "C:\Art\" & ThisWorkbook.Name & Wks.Name & ".pdf"
    Next Wks
End Sub

In any case, thanks for the feedback, glad having been of some help.

 

[Artisez]

Also, just to avoid misunderstandings in VBE, it's best not to use variable names that are the same as keywords used in Excel macros. Instead of "Worksheet" use something like this:

Option Explicit
Sub SaveEachWorksheetAsPdfFile()
    Dim Wks As Worksheet
    For Each Wks In Worksheets
        If Not Wks.Visible = xlSheetHidden Then Wks.ExportAsFixedFormat xlTypePDF, "C:\Art\" & ThisWorkbook.Name & Wks.Name & ".pdf"
    Next Wks
End Sub

In any case, thanks for the feedback, glad having been of some help.

Thank You!