VBA Excel advance filter data and copy to worksheets

[TaskMaster]

Hi all,

I have a data set that contains a years worth of data, I would like to filter the data by month and paste this to its own sheet, my data is columns A-N and my months are listed in column M.

I have a range in column R which also lists the months Jan-Dec.

However I get an error on this line - Sheets(sht).Range("M1:M" & last).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("R1"), Unique:=True

"Run-time error '1004': The extract range has a missing or illegal field name."

Does anyone know how to fix?

Sub filter()

Application.ScreenUpdating = False
Dim x As Range
Dim rng As Range
Dim last As Long
Dim sht As String

sht = "Data"

last = Sheets(sht).Cells(Rows.Count, "M").End(xlUp).Row
Set rng = Sheets(sht).Range("A1:N" & last)

Sheets(sht).Range("M1:M" & last).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("R1"), Unique:=True

For Each x In Range([R2], Cells(Rows.Count, "R").End(xlUp))
With rng
.AutoFilter
.AutoFilter Field:=13, Criteria1:=x.Value
.SpecialCells(xlCellTypeVisible).Copy

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

Sheets(sht).AutoFilterMode = False

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

End Sub

 

[TaskMaster]

Hi Yes, cells in column B will either display "Match" or "Difference"

 

[Fluff]

Ok, how about

Sub TaskMaster()
   Dim Cl As Range
   Dim Dic As Object
   Dim i As Long
   
   Application.ScreenUpdating = False
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Data")
      For Each Cl In Range("M2", .Range("M" & Rows.Count).End(xlUp))
         Dic(Cl.Value) = Empty
      Next Cl
      For i = 0 To Dic.Count - 1
         .Range("A1:N1").AutoFilter 2, "Difference"
         .Range("A1:N1").AutoFilter 13, Dic.Keys()(i)
         Sheets.Add(, Sheets(Sheets.Count)).Name = Dic.Keys()(i)
         .AutoFilter.Range.Copy Sheets(Dic.Keys()(i)).Range("A1")
      Next i
      .AutoFilterMode = False
   End With
End Sub

 

[TaskMaster]

Hi Fluff - Just tested and works perfectly. Thank you for all your help!

 

[Fluff]

My pleasure.