trouble with variable sheet name

Hi all,
I have a workbook that downloads information from the internet and places it on a spreadsheet. That part works fine, but I am also trying to include a filter in the process. I am using a string variable (that may not be the correct term), that is used in the url and also is the name of the sheet where the filtered data is supposed to end up. Abbreviated code as follows (i did not include the query part of the code):

Sub GetData()

Dim strFin(1 To 3) As String
    strFin(1) = "basicmaterials"
    strFin(2) = "consumergoods"
    strFin(3) = "financial"

For Z = 1 To 3

Dim RowCt As Integer
Dim RowTot As Variant

Sheets("Raw").Activate
RowTot = ActiveSheet.UsedRange.Rows.Count

For RowCt = 2 To RowTot
        If (Sheets("Raw").Cells(RowCt, 7).Value < 0 And Sheets("Raw").Cells(RowCt, 5).Value > 0 And _
            Sheets("Raw").Cells(RowCt, 4).Value > 0) Then
                Range("A" & RowCt & ":" & "K" & RowCt).Select
        Selection.Copy Destination:=Sheets(strFin(Z)).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    End If
Next RowCt

Next Z

End Sub

I am getting a Run-time error '9' Subscript out of range in the red line of code.

This worked when I tested it with a specific sheet name (e.g. "basicmaterials"), but it doesn't this way.

Thanks.

Here is a way that you can check to see what sheet names don't match up. Just run the code:

Sub Show_Missing_Sheets()
Dim Z As Integer
Dim strSheet As String
Dim strFin(1 To 3) As String
strFin(1) = "basicmaterials"
strFin(2) = "consumergoods"
strFin(3) = "financial"
    
    
For Z = 1 To 3

If Not Evaluate("=ISREF('" & strFin(Z) & "'!A1)") = True Then
    strSheet = strSheet & " " & strFin(Z) 'sheet names that weren't found
End If

Next Z

MsgBox (strSheet)

End Sub

Thanks for your responses. I ran the code suggested by stnkynts and got no matches, so I assume that if i did have an errant space before or after the name that it would have showed that. However, just to be sure I also checked manually for extra spaces. All good on that count.

I tried the modification suggested by 6StringJazzer but ended up with the same Run-time error. My thinking then was to break that line down to see where the error is occuring. Modified as follows:

For RowCt = 2 To RowTot
        If (Sheets("Raw").Cells(RowCt, 7).Value < 0 And Sheets("Raw").Cells(RowCt, 5).Value > 0 And _
            Sheets("Raw").Cells(RowCt, 4).Value > 0) Then
                Range("A" & RowCt & ":" & "K" & RowCt).Select
        Selection.Copy
        Sheets(strFin(Z)).Activate
        Range("A" & Sheets(strFin(Z)).Rows.Count).End(xlUp).Offset(1, 0).Select
        ActiveSheet.Paste
    End If
Next RowCt

Run-time error occurred on red line. I also tried: Dim sheetName as String and then sheetName = strFin(Z) thinking that maybe that would push it through, but alas same result.

I honestly cannot see what is wrong with that part. Still stumped.

Hi protonLeah,

I tried this more or less, but got the same error. The modification I had to make was because the the Next Z statement actually needs to be outside the With statement. Perhaps I should have done this earlier, but here is the code in it's entirety:

Sub GetFinvizData()

Dim Z As Integer
Dim strFin(1 To 8) As String
    strFin(1) = "basicmaterials"
    strFin(2) = "consumergoods"
    strFin(3) = "financial"
    strFin(4) = "healthcare"
    strFin(5) = "industrialgoods"
    strFin(6) = "services"
    strFin(7) = "technology"
    strFin(8) = "utilities"
Dim RowCt As Integer
Dim RowTot As Variant

For Z = 1 To 8

'Delete existing data

Sheets("Raw").Activate
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

'Download Sector Stock Data

strFin(Z) = "http://finviz.com/export.ashx?v=151&f=sec_" & strFin(Z) & ",sh_avgvol_o500,sh_opt_optionshort,sh_price_o10&ft=4&o=industry&c=1,2,4,42,43,44,45,46,47,65,68"
QueryQuote:
            With Sheets("Raw").QueryTables.Add(Connection:="URL;" & strFin(Z), Destination:=Sheets("Raw").Range("a1"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
            End With

Sheets("Raw").Range("a1").CurrentRegion.TextToColumns Destination:=Sheets("Raw").Range("a1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, other:=True, OtherChar:=",", FieldInfo:=Array(1, 2)
       
Sheets("Raw").Columns("A:B").ColumnWidth = 12
Range("A1").Select

'Filters bullish potential trades based on:
    '-Week and month performance positive
    '-Half performance negative
    
    With Sheets("Raw")
        RowTot = .UsedRange.Rows.Count
                
            For RowCt = 2 To RowTot
                If (.Cells(RowCt, 7).Value < 0 And .Cells(RowCt, 5).Value > 0 And .Cells(RowCt, 4).Value > 0) Then
                    .Range("A" & RowCt & ":" & "K" & RowCt).Copy _
                        Destination:=Sheets(strFin(Z)).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                End If
            Next RowCt
        
    End With
Next Z

End Sub

I think the With statement is the way to go, but it resulted in the same error when the Next Z was outside of the last With. Again the query works fine. The idea is that "Raw" is the dump sheet for the data before filtering. Once the filter is complete, then "Raw" is cleared and the next set of data is brought in and will run through the same filter and placed on the sheet with the sector name. Ultimately there will be approximately 6 different filters run on the data before I get to the Next Z. I have not written the other filters yet, since I think if I can get one to work the others will follow suit.

Thanks.

Ok I am an idiot. As I was reviewing my last post, I noticed that I set the value of strFin(z) as the url. Works fine when i use a different string for the url. I am kind of surprised that it didn't kick out an error on that debacle. Anyway, thanks all for your help.

Exactly. Thanks protonLeah. used strFinSite as string for url. pretty much how i felt when i saw it.