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

 

[Sektor]

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.

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

 

[Norie]

Eoin

Have you tried creating an array from scratch instead of a string, then an array.

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

 

[eoinymc]

That works Rory!

Thanks so much...

Eoin