VBA Help - Advanced Filtering

[Mattandy55]

Hello, I have the below code where I am trying to advance filter a final output "Motus Raw" worksheet. The process is a user will copy and paste new data and overwrite previous data on the "Motus Raw Data" sheet. Then the criteria I want to advance filter on is in my "lists" sheet. The raw data sheet will have data from A1: CR, with row 1 being headers in my table and it could be different row lengths. Currently, when I run the code below, it seems to function properly except it doesn't seem to be using the criteriaRange it just paste A1:CR to the destWS. Any help would be greatly appreciated. Current code below.



Dim sourceWs As Worksheet
Dim criteriaWs As Worksheet
Dim destWs As Worksheet
Dim criteriaRange As Range
Dim lastRow As Long

' Set references to the worksheets
Set sourceWs = ThisWorkbook.Sheets("Motus Raw Data")
Set criteriaWs = ThisWorkbook.Sheets("Lists")
Set destWs = ThisWorkbook.Sheets("Motus Raw")

' Set the criteria range on the "Lists" sheet
Set criteriaRange = criteriaWs.Range("A1:H1")

' Find the last row of the source data using the table's ListObject
lastRow = sourceWs.ListObjects("Table1").ListRows.Count

' Clear previous filter results in the destination sheet
destWs.Cells.Clear

' Apply the advanced filter using the criteria range
sourceWs.ListObjects("Table1").Range.AdvancedFilter Action:=xlFilterCopy, _
criteriaRange:=criteriaRange, CopyToRange:=destWs.Range("A1"), Unique:=True

Thanks,

Matt

 

[DanteAmor]

Set criteriaRange = criteriaWs.Range("A1:H1")

In row 1 are the headings that you are going to use for the filter and in rows 2 onwards the criteria. But you only put A1:H1, if the criteria is in row 2 then it should be:

Set criteriaRange = criteriaWs.Range("A1:H2")

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------