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
 
Try this. If ya wanna check for empty cell, never use <>"" method, 'cause in a cell can be a formula which returns empty string, but the cell itself isn't empty.

Code:
Sub G()

    Dim ArrSheets() As String, i As Long, j As Long
    Dim s As String
    
    For i = 3 To 7
        If IsEmpty(Worksheets(i).Range("F4")) Then
            j = j + 1
            ReDim Preserve ArrSheets(j)
            ArrSheets(j) = Worksheets(i).Name
        End If
    Next i
    
    If IsEmpty(Worksheets(9).Range("D6")) Then
        ReDim Preserve ArrSheets(j + 1)
        ArrSheets(j + 1) = Worksheets(9).Name
    End If
        
    For i = LBound(ArrSheets) To UBound(ArrSheets)
        Worksheets(i).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\Data\FileOutput.pdf", Quality:= _
            xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
    Next

End Sub
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Eoin

Have you tried creating an array from scratch instead of a string, then an array.
Code:
Dim I As Long
 
    ArrSheets = Array("Cover")
 
    For I = 3 To 7
        If Worksheets(I).Range("F4").Value <> "" Then
            ReDim Preserve ArrSheets(UBound(ArrSheets) + 1)
            ArrSheets(UBound(ArrSheets)) = Worksheets(I).Name
 
        End If
    Next I
 
    If Worksheets(9).Range("D6").Value <> "" Then
        ReDim Preserve ArrSheets(UBound(ArrSheets) + 1)
        ArrSheets(UBound(ArrSheets)) = Worksheets(I).Name
    End If
 
    Sheets(ArrSheets).Select
 
Upvote 0

Forum statistics

Threads
1,224,609
Messages
6,179,881
Members
452,948
Latest member
Dupuhini

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