Sheets(Array(...)).Select Question

eoinymc

Board Regular
Joined
Jan 29, 2009
Messages
203
Hi,

I am trying to use the following code, but I keep getting the "Subscript Out of Range" error.

I'm sure it has to do with the my variable:

The code basically looks in each worksheet to see if there is data in it and if there is, I want to print that to PDF.

Code:
Dim ArrSheets As String
ArrSheets = Chr(34) & "Cover" & Chr(34)
    
For i = 3 To 7
 If Worksheets(i).Range("F4").Value <> "" Then
  ArrSheets = ArrSheets & ", " & Chr(34) & Worksheets(i).Name & Chr(34)
 End If
    
Next i
    
If Worksheets(9).Range("D6").Value <> "" Then
 ArrSheets = ArrSheets & ", " & Chr(34) & Worksheets(9).Name & Chr(34)
End If
    
Sheets(Array(ArrSheets)).Select
            
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
        "C:\Data\FileOutput.pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

When I hardcode in the following into the Sheets(Array(...)).Select line, it works:

Code:
Sheets(Array("Cover", "Region 1", "Region 2", "Region 3", "Other")).Select

so I can only assume that the problem is with my ArrSheets variable...any ideas as to why this doesn't work?

If you need anymore info, please let me know.

Thanks,

Eoin
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Try amending the line to:

Code:
Sheets(Split(ArrSheets,", ")).Select

You haven't created an array - you created a string which you need to convert into an array. Using Array() on the string results in a single element array containing the entire string within ArrSheets.
 

eoinymc

Board Regular
Joined
Jan 29, 2009
Messages
203
Thanks Richard..

Is that meant to be Split instead of aplit?

Eoin

Edit: Just see you changed it...!
 

eoinymc

Board Regular
Joined
Jan 29, 2009
Messages
203

ADVERTISEMENT

Richard,

That doesn't work unfortunately..I get the same error as before.

Do I need to dim the variable as an Array or something like that?

Do you think it is viable to get this working by making some minor adjustments to my code? Or would I need to try some other way?

Thanks,

Eoin
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,099
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Try:
Code:
Dim ArrSheets As String
ArrSheets = Chr(34) & "Cover" & Chr(34)
    
For i = 3 To 7
 If Worksheets(i).Range("F4").Value <> "" Then
  ArrSheets = ArrSheets & "," & Worksheets(i).Name
 End If
    
Next i
    
If Worksheets(9).Range("D6").Value <> "" Then
 ArrSheets = ArrSheets & ", " & Chr(34) & Worksheets(9).Name & Chr(34)
End If
    
Sheets(Split(ArrSheets, ",")).ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
        "C:\Data\FileOutput.pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
 

eoinymc

Board Regular
Joined
Jan 29, 2009
Messages
203

ADVERTISEMENT

Thanks Rory...still doesn't work unfortunately!

I get the same error again...

Any other suggestions?

Cheers,

Eoin
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,099
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Sorry, I missed one:
Rich (BB code):
Dim ArrSheets As String
ArrSheets = "Cover"
    
For i = 3 To 7
 If Worksheets(i).Range("F4").Value <> "" Then
  ArrSheets = ArrSheets & "," & Worksheets(i).Name
 End If
    
Next i
    
If Worksheets(9).Range("D6").Value <> "" Then
 ArrSheets = ArrSheets & ", " & Chr(34) & Worksheets(9).Name & Chr(34)
End If
    
Sheets(Split(ArrSheets, ",")).ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
        "C:\Data\FileOutput.pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

Obviously this assumes you have a sheet called Cover.
 

eoinymc

Board Regular
Joined
Jan 29, 2009
Messages
203
Thanks again Rory..still no joy!

I'm getting a different error this time "Object doesn't support this property or method"

Also, I changed the line:

FROM:
Code:
ArrSheets = ArrSheets & ", " & Chr(34) & Worksheets(9).Name & Chr(34)

TO:
Code:
ArrSheets = ArrSheets & "," & Worksheets(9).Name

I really appreciate the help...any other ideas?

Cheers,

Eoin
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,099
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Should have looked closer - you can either export one sheet or the entire workbook as PDF. Try changing the last bit to:
Code:
Sheets(Split(ArrSheets, ","))Copy
Activeworkbook.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
        "C:\Data\FileOutput.pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,251
Messages
5,769,033
Members
425,511
Latest member
jpemberton

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
Top