Advanced filtering not working when a cell is focused on Pivottable...

I have made a search page using advanced filtering in VBA. The search results are displayed from 5 different sheets based on user's choice. Every thing works well except for one condition. The 5 sheets are Purchase, Sales, SReturn, Issued, Recv, each sheet has the data and a pivottable representing the data. Now i select a cell on the pivottable on the purchase sheet and now i go to the search page and select Purchase and click on search. If i do this i get an error, whereas if i select any cell on the data portion ( away from the pivottable ) and again make a search it works. Do the pivottable and advanced filtering has any relation?. I need any ones help to understand why this happens.

I have attached the file for your reference...

One more thing, when i filter using dates it works only when i enter the date in MM-DD-YYYY format, other formats not working. Is it possible to make it work using DD-MM-YYYY format.

Regards
Sundar

The DD-MM-YYYY format is controlled in the option section of EXCELL

Hi RCM,

I could not find any such setting in the Excel Options. I can see only language settings, can you tell me where exactly the option is located.

Regards
Sundar

You are right, my mistake . Excell follows the Operating system standards

it can be found in

>Control panel>Change date, times.....

Hi RCM,

My regional date format settings are set to DD-MM-YYYY format only. Even i have formatted my data (date field) in dd-MM-YYYY format only. Any suggestion on this.....