下标超出范围(错误9)

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

下面的代码不起作用，我的下标超出了范围(错误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

需要帮助这应该是运行一个高级过滤器并将结果粘贴到新创建的表中，选择中的原始表具有从A1到G11的数据

2 个解决方案

#1

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:

如注释中所述，创建工作表将使其获得关注。您还需要将标题复制到工作表中，以便excel知道将值放在哪里:

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

#2

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.

我知道可以改进它以提高效率，但出于某种原因，我无法解释这是适用于我的代码。

#1