Excel VBA - Autofilter, copy, paste to new sheet

[ozalonazol]

Hi! My VBA knowledge is very basic so I am really lost about how to solve my problem. I am trying to filter values from a large dataset, copy them and paste them to a new sheet. I found a code in an old threat https://www.mrexcel.com/forum/excel...ications-autofilter-copy-paste-new-sheet.html which i tried to use but it hasn't worked. The code I am using is the following and the error I get is "Run-time error '1004': The extract range has a missing or invalid field name". My data is in the range A1:BH3921 and column A contains the unique values that I want to filter. There are multiple rows containing each value.

Could you please help me to find the error or suggest another code that serves for the same purpose?

Thanks in advance for your help!

Sub Foo()
    Dim c As Range
    Dim rng As Range
    Dim LR As Long
        
        LR = Cells(Rows.Count, "R").End(xlUp).Row
        Set rng = Range("A1:BH" & LR)
        
        Range("A1:A" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AM1"), Unique:=True
        
        For Each c In Range([AM2], Cells(Rows.Count, "AM").End(xlUp))
            With rng
                .AutoFilter
                .AutoFilter Field:=1, Criteria1:=c.Value
                .SpecialCells(xlCellTypeVisible).Copy
                Sheets.Add(After:=Sheets(Sheets.Count)).Name = c.Value
                ActiveSheet.Paste
            End With
        Next c
        
End Sub

 

[vcoolio]

Hello Ozalonazol,

If you look carefully at the code in the old thread, you'll notice that the unique values have been extracted from Column E and copied to Column AM. This is a column outside the data set. In other words, a temporary helper column.

In your code above, you have left the helper column (AM) inside your data set hence creating conflict with the data that should be there and the data which needs to be unique (the criteria that is required to create/name the new sheets).

In this line of code:

Range("A1:A" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AM1"), Unique:=True

change the Column AM reference to a column outside your data set,e.g Column BJ.

In the following line:

 For Each c In Range([AM2], Cells(Rows.Count, "AM").End(xlUp))

change the column reference from AM to BJ.

See if that does the trick for you.

Cheerio,
vcoolio.

 

[ozalonazol]

Thanks vcoolio, that fixed it!

 

[vcoolio]

No worries Ozalonazol. Glad that I was able to help.

Cheerio,
vcoolio.