VBA How to save all sheets inside workbook except first 2, as pdf with names+date?

[paddingtonbear]

Is it possible to have VBA code do

1. Apply on each sheet in current workbook except first 2 sheets.
2. Save each sheet as .pdf with filename after sheet names + day after today + current time in hh:mm , like this:

Workbook . xlsm
[Sheet1 DONT TOUCH THIS PAGE
[Sheet2 DONT TOUCH THIS PAGE
[Sheet3 name "Shaggy Guy" > file export /like> Shaggy_2023.12.05_11:42pm.pdf
[Sheet4 name "Scooby Doo" > file export /like > Scooby_2023.12.06_11:42pm.pdf

And Save without user promt, inside the same folder as workbook file.
Would be much easier for my team to upload those pdf files on Sharepoint/Onedrive afterwards.


Best regards,
Paddingtonbear

 

[rlv01]

Yes. It is possible.

Sub DoSomething()
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim SavePath As String
    Dim FilePathName As String
    
    Set WB = ActiveWorkbook

    SavePath = "C:\Temp\"

    '[Sheet3 name "Shaggy Guy" > file export /like> Shaggy_2023.12.05_11:42pm.pdf
    '[Sheet4 name "Scooby Doo" > file export /like > Scooby_2023.12.06_11:42pm.pdf

    For Each WS In WB.Worksheets
    Debug.Print WS.Name
        Select Case WS.Name
        Case "Sheet1", "Sheet2"                       'sheets to ignore
        Case "Shaggy Guy", "Scooby Doo"
            FilePathName = SavePath & Split(WS.Name, " ")(0) & "_" & Format(Date, "yyyy.mm.dd") & "_" & Format(Time, "hh.mm") & ".pdf"
            WS.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FilePathName  ', OpenAfterPublish:=True
        End Select
    Next WS
End Sub

As far as saving to SharePoint or OneDrive, you are on your own for that.

 

[jolivanes]

@rlv01
I would change

Case "Shaggy Guy", "Scooby Doo"

to

Case Else

in case there are more then two additional sheets and hope that the examples, two words as sheet name, are representative.

 

[rlv01]

@jolivanes, I was not sure whether the OP was looking for an enforced 'whitelist' or not. I might have misunderstood.

 

[jolivanes]

It caught my eye because he/she mentions "each sheet in current workbook"
The more people comment on it, the more possibilities might come up!!!!!!!
I am sure he/she is happy with your suggestion.