Combine Tabs into PDF

alrichar

New Member
Joined
Dec 12, 2012
Messages
31
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".

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

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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?
 
Upvote 0
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)?



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

Code:
ThisWorkbook.Sheets(Array(Product1, Product2, Product3)).Select
What is the error message it is returning?

Run Time Error '9':

Subscript out of range
 
Upvote 0
Have you tried to replace the sheet names with their respective index numbers just to see if that works?
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top