Autosave Excel work sheet as PDF with file name pulled from cell (macro)

[remeng]

Hello,

I am sure that this has already been developed, but I couldn't find the answer or method.

Objective:

To create a macro in Excel that will automatically save each sheet as a separate file to a specific folder that is selectable with the file name pulled from a cell in the same sheet.

Example:

select save folder location

'I = the physical sheet number and not the sheet name

I = 1

Input: Sheet1, Cell A1 = Bob
Output: Bob.pdf

GOTO: I = I + 1

Input: Sheet2, Cell A1 = Frank
Output: Frank.pdf

-----

There are about 50 files that have 50 plus sheets so that is why I cannot do this manually. Each sheet is named differently too.

Thank you for the help,

Mike

 

[Andrzejek]

a macro in Excel that will automatically save each sheet as a separate [PDF] file to a specific folder [like C:\TEMP\] that is selectable with the file name pulled from a cell [A1] in the same sheet."

How about:

Option Explicit

Sub SaveSheetsAsPDF()
Const MYPATH As String = "C:\TEMP\"
Dim S As Integer

ChDir MYPATH

For S = 1 To Sheets.Count
    Sheets(S).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        MYPATH & Sheets(S).Range("A1") & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
Next S

End Sub

---- Andy

There is a great need for a sarcasm font.

 

[remeng]

Thanks Andy!

This is exactly what I was looking for.

One other thing; Instead of pre-setting the save folder, is there a way to make it so that the user can select the save folder?

Mike

 

[Andrzejek]

Sure, just include this logic to allow user to select any folder.

And your code may look like this:

Option Explicit

Sub SaveSheetsAsPDF()
Dim dlgSaveFolder As FileDialog
Dim sFolderPathForSave As String
Dim strMyPath As String
Dim S As Integer

Set dlgSaveFolder = Application.FileDialog(msoFileDialogFolderPicker)
With dlgSaveFolder
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = ThisWorkbook.Path & "\"
    If .Show <> -1 Then GoTo CancelFolderSelection
    sFolderPathForSave = .SelectedItems(1)
End With
Set dlgSaveFolder = Nothing

strMyPath = sFolderPathForSave & "\"

ChDir strMyPath

For S = 1 To Sheets.Count
    Sheets(S).ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=strMyPath & Sheets(S).Range("A1") & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
Next S
Beep
MsgBox "I am done."

Exit Sub
CancelFolderSelection:
MsgBox "no folder selected"

End Sub

---- Andy

There is a great need for a sarcasm font.

 

[remeng]

Hi Andy,

Everything is working well, but when I tried adding this into the personal excel file, the file opens to run the code, but then doesn't close.

If I add a close workbook command, it causes the code to not run properly.

Where should I add the code so that when someone clicks the icon to launch the macro, it is global?

Thanks for the help!

 

[Andrzejek]

I believe you also need to specify in the code which Excel's workbook you want to process.

You can use this code to select an Excel file.

---- Andy

There is a great need for a sarcasm font.