[tohotspur]

Hi guys,

I have a problem with VBA I need to overcome.

OK, so, I have three Worksheets:

Data_Sheet – this contains a table of data with three columns; Date, Sales, Cost
Pivot_Sheet – this contains a pivot table of the data within the Data worksheet
Controls_Sheet – This contains a parameter which I would like to use to control the pivot table

What I would like to do is to change the date value in Controls_Sheet!B2, then run a macro, which would then filter all date values except the value within Controls_Sheet!B2.

So for example, if these are my dates within the ‘Dates’ column of my Pivot Table:

31/07/2010
07/08/2010
14/08/2010
21/08/2010
28/08/2010
04/09/2010

Then I set Controls_Sheet!B2 equals ‘04/09/2010’ then run the macro, only the line for ‘04/09/2010’ will be showing in the pivot table.

I have tried many codes all which seem not to work, so I haven’t posted them here. I have posted the example work sheet.

I’m sure this is simple buy I’m struggling with it. Thanks in advance.