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
Bookmarks