Help with a Macro

[John M]

What is am trying to achieve:
I have a workbook with tabs named for each of my inspectors. What I would like to happen is when I run the macro, each worksheet is turned into a PDF named for the current date and saved in a specific drive/folder path.
I've written and tried one, but saves then entire workbook in the PDF and then stops when it gets to the file save window.
I am rather novice when it comes to writing or recording macros. Any input would be awesome...

 

[Luke M]

Hi John, and welcome to the forum!

Give this a try. Saves each sheet as a pdf, using sheet name + current date. Shown code has 2 settings for fPath...delete the one you don't want.

Sub MakePDFs()
Dim ws As Worksheet
Dim fPath As String
Dim fName As String

'Which folder to save in?
'Use this line to save in same path as workbook
fPath = ActiveWorkbook.Path
'Or, use this line to choose the path
fPath = "C:\My Documents\PDF Files"

'error check
If Right(fPath, 1) <> "\" Then
    fPath = fPath & "\"
End If

For Each ws In ActiveWorkbook.Worksheets
    'Build PDF name
    fName = ws.Name & "_" & Format(Date, "yyyymmdd") & ".pdf"
    'Create PDF
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & fName, openafterpublish:=False
Next ws
End Sub