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.
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.