Combine Tabs into PDF

[alrichar]

Hello. I am working on a macro that combines data on individual tabs into one pdf. However, I am also wanting to make this code dynamic so I can use this macro for different product lines. I am wanting to use a variable that references a cell in ms excel that contains the name of the tab. For some reason, I am getting an error and I'm not sure why. Can somebody help me out with this issue?

I should note that all of the tabs that needs to be converted to a combined PDF start with "C".

Sub Save_As_PDF_Combined()
 
Dim wSheet As Worksheet
Dim Product1 As String
Dim Product2 As String
Dim Product3 As String
strPath = (Sheets("Input").Range("E72").Value)
StrName = (Sheets("Input").Range("E73").Value)
TabName = ActiveSheet.Name
MonthEndDate = (Sheets("Input").Range("H1").Value)
Product1 = (Sheets("Input").Range("C30").Value)
Product2 = (Sheets("Input").Range("C31").Value)
Product3 = (Sheets("Input").Range("C32").Value)
 
 For Each wSheet In Worksheets
        If wSheet.Name Like "C*" Then
wSheet.Select
ThisWorkbook.Sheets(Array(Product1, Product2, Product3)).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
    strPath & "\" & StrName, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
     IgnorePrintAreas:=False, OpenAfterPublish:=False
  
End If
Next wSheet
  
 End Sub

 

[Joe4]

I am wanting to use a variable that references a cell in ms excel that contains the name of the tab.

Where is that in your code (what is the variable name)?

For some reason, I am getting an error and I'm not sure why.

Which line is returning the error?
What is the error message it is returning?

 

[alrichar]

I made a couple of corrections to my response.

Hello. Below are my responses. Thank you for your help.

Where is that in your code (what is the variable name)?

Product1 = (Sheets("Input").Range("C30").Value)
Product2 = (Sheets("Input").Range("C31").Value)
Product3 = (Sheets("Input").Range("C32").Value)
 
ThisWorkbook.Sheets(Array(Product1, Product2, Product3)).Select

Which line is returning the error?

This line has the error

ThisWorkbook.Sheets(Array(Product1, Product2, Product3)).Select

What is the error message it is returning?

Run Time Error '9':

Subscript out of range

 

[jolivanes]

Have you tried to replace the sheet names with their respective index numbers just to see if that works?

 

[Joe4]

Confirm that the values in cells C30:C32 EXACTLY match tab names in your Workbook (any extra spaces will throw it off), and that all of those sheets you are trying to select are visible (none hidden).