VBA Filter Spreadsheet by values, then create new tabs

[ChloeSpurge]

Hi, The bellow code is meant to filter to each value in column J, then copy the data into a new excel tab. However it gets stuck on
Sheets(sht).Range("J1:J" & last).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AN1"), Unique:=True

Any ideas how I could fix it? FYI my data's last column is AM.

Sub filter()
Application.ScreenUpdating = False
Dim x As Range
Dim rng As Range
Dim last As Long
Dim sht As String

'specify sheet name in which the data is stored
sht = "CR Form 2022"

'change filter column in the following code
last = Sheets(sht).Cells(Rows.Count, "J").End(xlUp).Row
Set rng = Sheets(sht).Range("A1:AM" & last)

Sheets(sht).Range("J1:J" & last).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AN1"), Unique:=True

For Each x In Range([AN2], Cells(Rows.Count, "AN").End(xlUp))

With rng
.AutoFilter
.AutoFilter Field:=10, Criteria1:=x.Value
.SpecialCells(xlCellTypeVisible).Copy

Sheets.Add(After:=Sheets(Sheets.Count)).Name = x.Value
ActiveSheet.Paste
End With
Next x

' Turn off filter
Sheets(sht).AutoFilterMode = False

With Application
.CutCopyMode = False
.ScreenUpdating = True
End With

End Sub





Many thanks

 

[Alex Blakenburg]

What do you have in J1 (the column J heading) ?
It is indicating that you have "special" characters in that cell.

 

[ChloeSpurge]

What do you have in J1 (the column J heading) ?
It is indicating that you have "special" characters in that cell.

Hi Alex,

I have the column header that is 'Contractor'.

many thanks

 

[Alex Blakenburg]

OK my guess is that the column name changed and you have something different to that in AN1.
If that is the case add the clear content line as shown below.
If your data that used to run to AM now runs to AN or further right, then we need to address where we are putting the output of the unique values.

Note: The 2nd line if just to show where to put the clear contents

    Range("AN1", Cells(1, "AN").End(xlDown)).ClearContents 
    
    Sheets(sht).Range("J1:J" & last).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AN1"), Unique:=True