Change this macro so it selects sheets from a list

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,575
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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.

Code:
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,768
Does this work?

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,462
Messages
5,528,942
Members
409,848
Latest member
Blomsten
Top