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

[eoinymc]

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.

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:

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

 

[eoinymc]

Anyone...anyone?...Bueller?...Bueller?...Frye?...Frye?

 

[Richard Schollar]

Try amending the line to:

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]

Thanks Richard..

Is that meant to be Split instead of aplit?

Eoin

Edit: Just see you changed it...!

 

[eoinymc]

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]

Try:

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]

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

I get the same error again...

Any other suggestions?

Cheers,

Eoin

 

[RoryA]

Sorry, I missed one:

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]

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:

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

TO:

ArrSheets = ArrSheets & "," & Worksheets(9).Name

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

Cheers,

Eoin

 

[RoryA]

Should have looked closer - you can either export one sheet or the entire workbook as PDF. Try changing the last bit to:

Sheets(Split(ArrSheets, ","))Copy
Activeworkbook.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
        "C:\Data\FileOutput.pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False