Macro to pull in the worsheet name to save it as a PDF.

[aadeshgandhi]

Hello,
When I run the macro I would it to save it to a PDF and also choose the name for a PDF based on the worsheet name. I have the macro setup in which its prints the selected sheet but I have to manually enter the name for each PDF. How to write to code so that the name = worksheet name? Below is the code.
Printer can be either Adobe PDF or CutePDF.

Sub PrintAll()
     Application.Dialogs(xlDialogPrinterSetup).Show
    Dim ws As Worksheet
    Application.DisplayAlerts = False
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name = "t1" Or ws.Name = "t2" Or ws.Name = "Sheet11" Or ws.Name = "Sheet12" Then
        Else
            ws.Visible = xlSheetVisible
            WHAT SHOULD GO HERE SO THAT IT PULLS IN THE WORKSHEET NAME FOR THE SAVE AS DIALOG BOX THAT OPENS UP. 
            ws.PrintOut
            
        End If
    Next ws
    Application.DisplayAlerts = True
End Sub

Thanks,
Aadesh Gandhi

[SEOSEO.dk]

I do NOT have the answer - but exactly the same issue. Look forward to see the administrators answer! Thank you aadeshgandhi to post this issue.

[aadeshgandhi]

I do NOT have the answer - but exactly the same issue. Look forward to see the administrators answer! Thank you aadeshgandhi to post this issue.

SEOSEO,
I was able to get two ways for it to work,
1) This will save the PDF in the same directory as the worksheet.

Sub SheetsToPDFs()

    Dim strPath As String
    Dim ws As Worksheet

    strPath = ActiveWorkbook.Path & "\"
    
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name = "A" Or ws.Name = "B" Or ws.Name = "C" Or ws.Name = "D" Or ws.Name = "E" Or ws.Name = "F" Then
        Else
        ws.ExportAsFixedFormat xlTypePDF, strPath & ws.Name & ".pdf"
        End If
    Next ws
        
End Sub

2) This will save the PDF to the desktop.

Sub SheetsToPDFs()

    Dim DeskTop As String
    DeskTop = CreateObject("WScript.Shell").SpecialFolders("Desktop")
    Dim ws As Worksheet

    
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name = "A" Or ws.Name = "B" Or ws.Name = "C" Or ws.Name = "D" Or ws.Name = "E" Or ws.Name = "F" Then
        Else
        ws.ExportAsFixedFormat xlTypePDF, Filename:=DeskTop & Application.PathSeparator & ws.Name & ".pdf"
        End If
    Next ws
        
End Sub

I hope this works for you.



Thanks,
Aadesh Gandhi