Help with Advanced Filter VBA

[arvarr]

I have an excel macro set up to perform advanced filter.


Firstly, I perform an advanced filter to move unique records for two columns (A = Employee, B = Period).
Then, I record another macro to perform advanced filter on the column A to obtain just the unique records.


After recording the code, I tried to run it again and it works (producing the list of filtered results).

But, if i delete the records then run the macro, it does not work and it does not come up with error message, it is just producing blank results.

It seems strange that I have to record the macro again manually and it works, but if the first step of obtaining the records are deleted, then it just produces a blank result.

Can someone please kindly help?

Thanks.

 

[xlbo]

Please post the recorded code

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[arvarr]

Hi xlbo
This is the code.

Sub NumofPeople()
Sheets(Weeks).Select
Range("B9").Select
Set FilterRange = Sheets("Unique Weeks").Range("A8:A3000")
Set TargetRange = Sheets("Unique Weeks").Range("F8")
FilterRange.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=TargetRange
End Sub

Step 1 - I perform an advanced filter to pull 2 columns of data into worksheet named Weeks.
Step 2 - Then, I tried to perform another advanced filter from the codes above to obtain unique records for just Employee.

Problem - If I perform the advanced filter manually from Data>Filter>Advanced Filter, it works ok.
But, if i perform it from the macro, it just resulted in blank results.

Can you please kindly help?
Thanks.

 

[xlbo]

Not sure - this isn't the whole code is it as it is just doing the 2nd advanced filter

there are a couple of issues but not to do with the issue you describe. the 1st is that you are not referencing the sheet correctly:

Sheets(Weeks).Select

should throw an error - you should either use

Sheets("Weeks").select

or

Weeks.Select

depending on how you have named your sheets

The other is that you are not specifying unique values in your code - I created a sheet called unique weeks and ran the following code without issue:

Set FilterRange = Sheets("Unique Weeks").Range("A8:A3000")
Set TargetRange = Sheets("Unique Weeks").Range("F8")
FilterRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=TargetRange, [b]unique:=True[/b]

Please provide the full code you are using for both advanced filters along with a small sample of the data that it is running over

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question