Macro to select all worksheet and save file as file name

[Luis Ferreira]

Good Afternoon,

I want to create a macro that will select all the worksheets (names and quantity will vary) and saves the file as the current file's name but in PDF. Since I only know how to record a macro it specifies the worksheet names but I need it for various workbooks. The name will vary plus the number of tabs can go anywhere from 3 to 40.

Thank you,

Luis

 

[Mohammad Basem]

Hi Luis,

Try this macro. Copy it to a Module code window

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Option Explicit

Sub SaveWorkbookAsPDF()

    Dim sFileName As String, _
        sFolderName As String

    [COLOR="green"]'* Select folder[/COLOR]
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Title = "Select a Folder"
        .Show
        If .SelectedItems.Count = 0 Then
            Exit Sub
        Else
            sFolderName = .SelectedItems(1)
        End If
    End With

    [COLOR="green"]'* Check if file already exists[/COLOR]
    sFileName = Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".") - 1) + ".pdf"
    If Dir(sFolderName + "\" + sFileName, vbNormal) = sFileName Then
        If MsgBox("The file """ + sFolderName + "\" + sFileName + """ already exists." + _
            Chr(10) + "Do you want to overwrite it?", vbYesNo, "File Exists") = vbNo Then
            Exit Sub
        End If
    End If

    [COLOR="Green"]'* Save as PDF[/COLOR]
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=sFileName, Quality:=xlQualityStandard, _
        OpenAfterPublish:=True

End Sub[/COLOR][/SIZE][/FONT]

 

[Davidwazza]

Just out of interest, what would the code look like if you only wanted to export a few of the many worksheets of your file to pdf, in the same pdf file? Cheers.

 

[Mohammad Basem]

First you need to loop through all sheets and select based on criteria, like

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]For i = 1 To Worksheets.Count
    If [I]Condition is True[/I] Then
        Worksheets(i).Select (False)
    End If
Next[/COLOR][/SIZE][/FONT]

then save as PDF using

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=sFileName, Quality:=xlQualityStandard, _
    OpenAfterPublish:=True[/COLOR][/SIZE][/FONT]

 

[Luis Ferreira]

Thank you so MUCH, I really appreciete it.

 

[Mohammad Basem]

Al-hamdullah, thank you for the feedback! All the best

 

[Luis Ferreira]

Good Afternoon Mohammad,

I get the error "Compile Error - Invalid Outside Procedure" on the line "with Application.FileDialog(msoFileDialogFolderPicker"

Sorry, I an new at this macro stuff

Thank you,

Luis

 

[Mohammad Basem]

Good afternoon Luis

Please make sure you have copied everything, and the code is bounded by Sub SaveWorkbookAsPDF() at the top and End Sub at the bottom

 

[Luis Ferreira]

Hi Mohammad,

Thank you, I did miss something when I copied. But when I save it it moves one folder back on the mapping.

I am so sorry for bodering you,

Thank you,

Luis

 

[Mohammad Basem]

It's OK, don't mention it. Most welcome