Copy each advance filtered data (filter by date 1 to 31) to new sheet

**Arshmaan** · 12-09-2011, 09:34 AM

Hello all,

I have a my employees monthly tracking report in excel worksheet and I have to do some advance filter on each date (1 to 31 except Sundays). I am doing these steps manullay:-

1) Do Advance filter (I’ve done some VBA work). For example I filter data by date 1st, so that my code filter data by date one and remove duplicates from column “f” as well.

2) Copy the filtered data.

3) Paste it on a new sheet.

I want to do this Via VBA.. or any suitable way.. please help… I am attaching my sheet as well from which you can understand my code and update it according to my need..

**jpr73** · 12-10-2011, 10:31 AM

Hi,
You will find attached a solution with Pivot Table
When the filter is done on the pivot table, the sheet copy is automaticaly done by the options command "Display pages of report filter"
I create the sheets Day Filter (01/10/2011) and the Month Filter with the procédure described above
I hope this will help
Best regards

**royUK** · 12-10-2011, 11:02 AM

Here's some code that you can adapt

Please Login or Register  to view this content.

**jpr73** · 12-12-2011, 09:14 AM

Hi,
Arshmaan you have cross posted here
Best regards

**Arshmaan** · 12-13-2011, 12:37 AM

Originally Posted by royUK

Here's some code that you can adapt

Please Login or Register  to view this content.

thanks roy for your help.. but

Set rData = .Range(Cells(1, 1).CurrentRegion)

Method 'Range' of object '_Worksheet' failed

will you please let me know how to fix it my table start from A4 i have also change the value but it didn't work.. i have attached my file as well

**Arshmaan** · 12-13-2011, 12:40 AM

Originally Posted by jpr73

Hi,
You will find attached a solution with Pivot Table
When the filter is done on the pivot table, the sheet copy is automaticaly done by the options command "Display pages of report filter"
I create the sheets Day Filter (01/10/2011) and the Month Filter with the procédure described above
I hope this will help
Best regards

Thank you very much pivot table works for me.. thanks alot.. ya i have also post there but i didn't receive any response

thanks to you..