Macros for selecting filters on a pivot table

[lizardd18]

Hi all,

I have the following problem:
I have a sheet with data which are being refreshed on a weekly basis.
From these data I have 5 pivot tables with different data, but same filters.
Then I create graphs from each one of there 5 pivot tables.
In order to have all 5 graphs synchronised (same filters, so that same data are being displayed in the graphs) I have created macros.
By clicking on one macro (example: Germany) all 5 pivots' filters become "Germany".
However, if the next week with the new data, I do not have Germany in my data, I have two cases:

1) I either receive a runtime error massage
2) The filter Germany appears in the pivots' filters' values, althought there is no Germany in the original data (of that week)

Can somebody please tel me what is going on, why the second case happens and how I can avoid it?

The reasoning behing avoiding a "fake" value in my pivot filters' values is that I want to create a file with all the possible filter values I can and then just add the data every week and make sure that if a value is not included in the data that week (e.g: Germany) the
pivot will not "create" a value that is not there and mess up the image of that week's data.

Thank you in advance.

[TMS]

Without seeing either the workbook or the code, it's difficult to suggest a reason or a solution.

I'd suggest that you include "On Error Resume Next" before your filter and, depending on the error code, take some avoidance action.

Regards

[lizardd18]

Hey TMShucks,

Thanks for your reply.Below you can find the code:

Sub MILL_STOCKSTADT()
'
' MILL_STOCKSTADT Macro
' Macro recorded 12/12/2010 by plolonis
'

'
Sheets("Pivots").Select
ActiveSheet.PivotTables("PivotTable5").PivotFields("Mill (R)"). _
CurrentPage = "STOCKSTADT"
ActiveSheet.PivotTables("PivotTable6").PivotFields("Mill (R)"). _
CurrentPage = "STOCKSTADT"
ActiveSheet.PivotTables("PivotTable7").PivotFields("Mill (R)"). _
CurrentPage = "STOCKSTADT"
ActiveSheet.PivotTables("PivotTable8").PivotFields("Mill (R)"). _
CurrentPage = "STOCKSTADT"
ActiveSheet.PivotTables("PivotTable9").PivotFields("Mill (R)"). _
CurrentPage = "STOCKSTADT"
Range("A1").Select
Sheets("Control Panel").Select
Range("F2").Select
ActiveCell.FormulaR1C1 = "STOCKSTADT"
With ActiveCell.Characters(Start:=1, Length:=5).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("F3").Select
End Sub

The problem is that if one week I do not have data for Mill Stockstadt and I click on the macro “MILL_STOCKSTADT” then the macro will create the value “STOCKSTADT” in the pivot values, even if it was not there in the beginning. Then the data that will be in the pivot will be the data of the selection before the selection of STOCKSTADT.
Example:
There are two mills: Stockstadt and Berlin. One week I have data only for the mill Berlin. Therefore, in the pivot, only the option Berlin will exist. However, if the user clicks on the macro button “STOCKSTADT” then the macro will create the “STOCKSTADT” value in the pivot and the data it will be displaying will be the ones of mill Berlin.

In order to test it, I created a macro with the value “TEST”. Of course there is not mill option with the name “TEST”. However, when I click on the “TEST” macro button, the value “TEST” is being created and selected in the pivot, while the data that is in the pivot is the data of the past selelction (“BERLIN” in this case).

I’d rather have a run-time error 1004 instead, so the user knows that this option is not available for this week.

I am puzzled because in other cases I do get this message, for another selection of the pivot.

I am afraid that the reason for this is that I created the Macros by copying the first one. In more detail: I create the first macro for the filed "Mill (R)" by recording and then I go to the code, copy and paste it and just change the name of the mill selection in the next macros. Maybe that is the source of the problem.

If anyone can help me I would really appreciate it.
Thank you, guys!

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[lizardd18]

Sorry for my late reply, but I was on holiday.
In order to make things easier to understand, I have created a file with an example.
I have in my data tab two options for each filter category:

1) Country: Germany or France
2) Product: Pens or Pencils

However, I assume that the week before I had in my data one more option per category:
1) Country: Spain
2) Product: Post it

In order to avoid making customised macros buttons every week (I would have to see every week what kind of options I get in my data and delete or create macro buttons accordingly) I would like to have all the options available and if there is no data for one option a specific week, then I would like to receive an error message or something that tells the user that this option is not valid (for that week).

The way I created the code is as follows:
I recorder the first macro and then I went to the code and copied-pasted it many times and I changed only the name of the options (country and product), the name of the pivot field (Product and Country) and the cell where the selection will be displayed.

Sometimes I receive an error message, but others the Post-it and the Spain options are being created, disrupting this way the data of the other fields. It is hard to explain, but if you click a couple of times on the macros buttons randomly and then check the data displayed you will unerstand what I mean.

The purpose of these macros is by clicking on one macro button to cynchronise both the Revenues and the Costs graphs.

Thank you in advance for your help.