vba excel to save sheets of a pdf file with the name of each sheet

Question

I don’t know much about VBA, but I need a routine that saves each of the spreadsheets of a file in separate Pdfs, for example:

The excel file RELATORIO has within it 24 spreadsheets (nominees: Ranking_1, Ranking_2, Ranking_3, ..., Ranking_24) there are still two spreadsheets base1 and base2 that should not be converted to pdf.

Searching on the Internet I found a routine, but I can’t let it automatically save the 24 files; in this routine I need to put the name to be saved in the order in which the tabs appear. Routine:

Sub Relatorio_PDF_v2()
Dim sh As Worksheet
Dim strNome As String 'Declaração do nome para abertura do box de inserção de número do registro
Dim Caminho As String 'Declaração endereço onde será salvo o documento
Dim Abrir As Boolean 'Declaração para abertura do registro após salvamento

  For Each sh In ThisWorkbook.Worksheets
  If VBA.LCase(VBA.Trim(sh.Name)) <> VBA.LCase("DADOS") And _
     VBA.LCase(VBA.Trim(sh.Name)) <> VBA.LCase("TABELA_DIN_GERAL") Then

 
       strNome = InputBox("Insira o nome do Relatório", "Gerador de Relatório em .pdf")
       Caminho = "C:\Users\" & Environ$("Username") & _
       "\Desktop\SAIDA_RANKING\" & "Ranking -  " & strNome
 
'Sai do processo de salvamento
   If strNome = "" Then
        MsgBox ("Salvamento Cancelado!!!")
    Exit Sub
    End If
       
            sh.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=Caminho, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=Abrir

 
   End If
   Next sh
End Sub

Answer 1

All right?

This is very simple to do! For the PDF file name to have the same name as the spreadsheet tab without you having to enter the name every time, just replace the code:

strNome = InputBox("Insira o nome do Relatório", "Gerador de Relatório em .pdf")

For: strNome = sh.Name

The command sh.Name captures the name of each spreadsheet tab.

I hope it helps!