I'm getting Subscript out of range (Error 9)

Question

the below code is not working I'm getting Subscript out of range (Error 9)

Sub advnextract()

Sheets.Add(Before:=ActiveSheet).Name = "Resultado"

Set extractto = ThisWorkbook.Worksheets("Resultado").Range("A5:G5")


    Selection.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "J1:J2"), CopyToRange:=extractto, Unique:=False

End Sub

Need help this is supposed to run a advanced filter and paste the result in the newly created sheet, the original table in the selection has data ranging from A1 to G11

When you add the new sheet it becomes active so your AF line is referring to the wrong sheet. You need to either re-activate the original sheet or, better, add sheet references. — SJR, Aug 03 '17 at 18:47
I did notice that and chenged it to `Sub advnextract() Sheets.Add(Before:=ActiveSheet).Name = "Resultado" Set extractto = ThisWorkbook.Worksheets("Resultado").Range("A5:G5") With Worksheets("Hoja1") Range("A1:G11").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _ "J1:J2"), CopyToRange:=extractto, Unique:=False End With End Sub` Still not working — Tom Ruiz, Aug 03 '17 at 18:54
Ouch, posting more than one line of code in the comments is hopeless I'm afraid. — SJR, Aug 03 '17 at 18:58
Not a strict duplicate, but relying on `Active` or `Selection` is very error prone, see: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros — David Zemens, Aug 04 '17 at 19:26

score 1 · Accepted Answer · answered Aug 03 '17 at 18:55

As stated in the comments, the creation of a worksheet causes that to gain focus. Also you need to copy the titles to the sheet so excel knows where to put the values:

Sub advnextract()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim rng As Range
Dim extractto as range

Set rng = Selection 'It is better to set an actual range instead of Selection.
                    'Also Selection must have at least 7 columns or this will error.
                    'It also needs to include the column headers in the Selection.
Sheets.Add(Before:=ActiveSheet).Name = "Resultado"

Set extractto = ThisWorkbook.Worksheets("Resultado").Range("A5:G5")
extractto.Value = rng.Rows(1).Value

    rng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=ws.Range( _
        "J1:J2"), CopyToRange:=extractto, Unique:=False

End Sub

And of course you should also check if your "Resultado" sheet does not already exist. — Jibato, Aug 03 '17 at 19:08
Still geting the error 9 in this line `Set extractto = ThisWorkbook.Worksheets("Resultado").Range("A5:G5")` — Tom Ruiz, Aug 04 '17 at 19:01
Please mark as correct by clicking the check mark by the answer. @TomRuiz — Scott Craner, Aug 04 '17 at 19:07

score 0 · Answer 2 · answered Aug 04 '17 at 19:09

After trial and error and some insight into some logic here is the final result

Sub advnextract()
Dim rng As Range

Set rng = Selection
Sheets.Add(Before:=Sheets("Hoja1")).Name = "Resultado"


   rng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Hoja1").Range("J1:J2"), _
   CopyToRange:=Sheets("Resultado").Range("A1"), Unique:=False

   Sheets("Resultado").Activate
   Columns("A:G").EntireColumn.AutoFit
   Range("A1").Select
End Sub

I know it can be improved to be more efficient but for some reason I can't explain this is the code that works for me.

https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros — David Zemens, Aug 04 '17 at 19:25

I'm getting Subscript out of range (Error 9)

2 Answers2