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]

Oh! Al-hamdullah, everything is OK

I am currently working on modifying the code that considers exaclty what you have just said, i.e., saving the PDF to the workbook folder.

But in this case I have to remove the folder selector window from the code. Correct?

Will revert to you soon, in-sha' Allah

 

[Luis Ferreira]

Yes, that is it.

I had a muslim friend that thought me some, but I can only speak it. I will try to type it but forgive me if something else comes out

Asah Walenkam (hope that it is a good try)

 

[Mohammad Basem]

Good for a first try, but could not get it May be you were trying to say Al-salamu Alaikum? Don't worry.

Here you go, try this version

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

Sub SaveWorkbookAsPDF()

    Dim sFileName As String, _
        sFolderName As String

    [COLOR="Green"]'* Get the folder of ThisWorkbook[/COLOR]
    sFolderName = Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "\"))

    [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

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

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

 

[Luis Ferreira]

THAT IS IT!!!!! THANK YOU SO MUCH.

Yes, that was my poor attempt to say "Al-salamu Alaikum"

 

[Mohammad Basem]

Al-hamdullah, you are most welcome. Wa alaikum al-salam, this is the reply. All the best