Change this macro so it selects sheets from a list

[tonywatsonhelp]

Hi Everyone,

I have a macro that selects two sheets and creates a PDF, its exactly what I need except I need it to pick a selection of sheets to use from a list,
I have a sheet called "Control"

Range is T2:U20

In column T I have a list of sheet names, in column U I have a Yes or No,
I want each Sheet that has a yes next to it to be included in the PDF.

Sub MultiPDF()

Sheets("Dashboard").Select
Sheets("Dashboard2").Select False       'select both sheets for PDF

Application.ScreenUpdating = False

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=ActiveWorkbook.Path & "\" & ActiveSheet.Range("AF5").Value & " " & Format(Now, "MM-DD-YYYY") & ".pdf", _
    OpenAfterPublish:=True
Application.ScreenUpdating = True
End Sub

Heres the code I have,

Please help if you can

Thanks

Tony

 

[Jeffrey Mahoney]

Does this work?

Sub MultiPDF()


  Dim Cel As Range
  Dim PDFShtRng As Range
  Dim Cntrl As Worksheet
  Dim Sht As Worksheet
  Dim A As String
  
  Set Cntrl = Worksheets("Control")
  Set Cel = Cntrl.Range("T2")
  Set PDFShtRng = Cntrl.Range(Cel, Cntrl.Cells(Cntrl.Cells.Rows.Count, Cel.Column).End(xlUp))
  
  Application.ScreenUpdating = False
  
  For Each Cel In PDFShtRng
    If UCase(Cel.Offset(0, 1).Value) = "YES" Then
      Set Sht = Worksheets(Cel.Value)
      
      Sht.ExportAsFixedFormat Type:=xlTypePDF, _
      Filename:=ActiveWorkbook.Path & "\" & Sht.Range("AF5").Value & " " & Format(Now, "MM-DD-YYYY") & ".pdf", _
        OpenAfterPublish:=True
    End If
  Next Cel
  
  Application.ScreenUpdating = True
End Sub