Distributing data into varying tabs VBA

Hello all,

Been a little while, but am back with a VBA question. I copied a generic VBA code (https://yodalearning.com/tutorials/e...to-split-data/) to distribute information from the attached sample file's "TestTab" using the 7th column's field "AssetClass" to new tabs per each "AssetClass" type.

I tried to modify the codee for my file as best I could but am getting stuck at the point where it filters and attempts to create a new sheet

rData.Copy Destination:=Worksheets(assetclass).Cells(1, 1)

Below is the full code

Sub ExtractToSheets()
    Dim ws          As Worksheet
    Dim wsNew       As Worksheet
    Dim rData       As Range
    Dim rfl         As Range
    Dim assetclass  As String
    Set ws = ThisWorkbook.Sheets("testtab")

    With ws
        Set rData = .Range(.Cells(1, 1), .Cells(.Rows.Count, 7).End(xlUp))
        .Columns(.Columns.Count).Clear
        .Range(.Cells(2, 7), .Cells(.Rows.Count, 7).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, .Columns.Count), Unique:=True
    
    'Calls Excel Advanced Filter
    
        For Each rfl In .Range(.Cells(1, .Columns.Count), .Cells(.Rows.Count, .Columns.Count).End(xlUp))
            assetclass = rfl.Text
    
            rData.AutoFilter Field:=7, Criteria1:=assetclass
            rData.Copy Destination:=Worksheets(assetclass).Cells(1, 1)
        Next rfl
    End With
    ws.Columns(Columns.Count).ClearContents
    rData.AutoFilter
End Sub

Also on the included sample file are three green tabs showing the expected results. Thank you for any help.
Kondukt

Hey Marc,

Thank you for the reply and help, but when I try this code instead it breaks at

.[A1].CurrentRegion.Columns(7).AdvancedFilter xlFilterCopy, , .[K1], True

asking to debug.

No issue on my side with your attachment …

My fault, had a different file's module open. It works. Thank you good sir.

Thanks for the rep' !

Hey Marc, sorry to rebump this thread but I was wondering if you would be willing to update the VBA on this for me? I tried modifying it to my final file but haven't been able to. Also, I added some formatting requests if it isn't too much trouble. I have attached a sample file that has all the same structures as my final file with an expected results tab for an example labeled "AssetClass FinalTab to build"

PS. I Amazon Prime'd a VBA book last night.