VBA Looping Through Specified Sheet

[amermarc]

Hi All

Looking for some help with a VBA macro and looping it through set sheets (but not all) in a workbook.

Context, I have a sheet which is calculating performance against set metrics for named users. The output is a summary page (individual sheet) for which i have written the below Macro to export the summary into a PDF with a set naming convention. The problem is the current Macro requires me to run it on each of the many sheets one by one, whereas what I would like to do is run a single Macro that will create all the individual summaries on one hit.

Its also possible that from time to time I will need to adjust the named sheets as new people are added to the plan.

It was suggested to me that I could record a macro that is running each individual macro but that doesn't feel very efficient.

Any help would be much appreciated as I've spent too long trying to find a solution with no joy.


CURRENT MACRO
Sub ExportToPDFV2()
'Exports a specific cell range as a PDF and defines the output path
Dim outputPath As String
outputPath = Application.GetSaveAsFilename(Range("R3").Value & ".pdf", "PDF Files (*.pdf), *.pdf")
Range("B15:O92").ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=outputPath, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub

 

[DanteAmor]

Hi and welcome to MrExcel.

With the following macro you must select the folder where all the pdfs will be stored. The name of each PDF will be taken from cell R3 of each sheet and the same range "B15:O92" of each sheet will always be saved.

In this line of the macro you must put the name of the sheets that you want to save as pdf.
Case "Sheet1", "Sheet2", "Summary", "Etc"

Sub ExportToPDF()
  Dim outputPath As String, sName As String
  Dim sh As Worksheet
 
  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select Folder"
    If .Show <> -1 Then Exit Sub
    outputPath = .SelectedItems(1) & "\"
  End With

  For Each sh In Sheets
    Select Case sh.Name
      'adjust the named sheets
      Case "Sheet1", "Sheet2", "Summary", "Etc"
        sName = sh.Range("R3").Value & ".pdf"
        sh.Range("B15:O92").ExportAsFixedFormat Type:=xlTypePDF, Filename:=outputPath & sName, _
          Quality:=xlQualityStandard, IncludeDocProperties:=True, _
          IgnorePrintAreas:=False, OpenAfterPublish:=False
    End Select
  Next
End Sub

 

[amermarc]

Sub ExportToPDF() Dim outputPath As String, sName As String Dim sh As Worksheet With Application.FileDialog(msoFileDialogFolderPicker) .Title = "Select Folder" If .Show <> -1 Then Exit Sub outputPath = .SelectedItems(1) & "\" End With For Each sh In Sheets Select Case sh.Name 'adjust the named sheets Case "Sheet1", "Sheet2", "Summary", "Etc" sName = sh.Range("R3").Value & ".pdf" sh.Range("B15:O92").ExportAsFixedFormat Type:=xlTypePDF, Filename:=outputPath & sName, _ Quality:=xlQualityStandard, IncludeDocProperties:=True, _ IgnorePrintAreas:=False, OpenAfterPublish:=False End Select Next End Sub

Amazing thanks you so much, this seems to solve my issues, wish i'd asked earlier.
Thanks again!