Macro in Excel with Pivot table

[Martin]

How can I ask the macro to run this the top sectionon every sheet that I
have. I want to create conditions in the top so I can change them easily and
run those conditions to the below sheets. (I have a total of 30 sheets)

With ActiveSheet.PivotTables("Tableau croisé
dynamique1").PivotFields("Country")
.PivotItems("Canada").Visible = False
.PivotItems("Europe").Visible = True
.PivotItems("US").Visible = True
.PivotItems("US/Can").Visible = True
.PivotItems("US/Canada").Visible = True
.PivotItems("(vide)").Visible = True


Sheets("Cktp E 26-32").Select
Sheets("Cktp E 30").Select
Sheets("Cktp E 36").Select
Sheets("Cktp G 26").Select
Sheets("Cktp G 30").Select
Sheets("Cktp G 36").Select
Sheets("Cktp I 30").Select
Sheets("Cktp I 36").Select

 

[Dave Peterson]

Every sheet (each and every sheet???) has a pivottable named exactly the same
thing???

If yes...

dim wks as worksheet
for each wks in activeworkbook.worksheets
with wks.pivottables("Tableau croisé dynamique1").pivotfields("country")
.pivotitems("canada").visible = ....
...
end with
next wks

if you only want to run this on worksheets that have a pivottable and those
worksheets have exactly one pivottable, you can use:

dim wks as worksheet
for each wks in activeworkbook.worksheets
if wks.pivottables.count > 0 then
with wks.pivottables(1).pivotfields("country")
.pivotitems("canada").visible = ....
...
end with
end if
next wks

If you have multiple pivottables per sheet and the name is not consistent, then
you'll have to be more careful--or name the pivottables nicely.

 

[Martin]

Tks you Dave, that what I was looking for !! In testing the macro, I found
out that some criteria are no in all Pivot, which give me a bug since I guess
that the macro try to select the criteria and can't.

How can I write it so it look at the pivot field that are true in my request
in the Pivotfield before doing the action. IF the field is not there, then
skip. (I put ==> where I got in trouble)


Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
==> If wks.PivotTables("Tableau croisé
dynamique1").DataPivotField("Country") = "Canada" or "US/Can" Then

With wks.PivotTables("Tableau croisé dynamique1").PivotFields("Country")
.PivotItems("Canada").Visible = True
.PivotItems("Europe").Visible = False
.PivotItems("US").Visible = False
.PivotItems("US/Can").Visible = True
End With
End If
Next wks

Tks Again for all your help !!

 

[Dave Peterson]

I usually just weasel out when I know that not all the fields may be present.

I'll use:

On error resume next
..pivotitems(...).visible = ...
....
on error goto 0

I turn error checking off for as little as I can and turn it back on as soon as
I know that it's important again.

You could check to see if that each was a valid pivotitem, but (for me), I'd end
up using a variation of the "on error resume next" stuff.

set testitem = nothing
on error resume next
Set testitem = .PivotItems("Canada")
on error goto 0
if testitem is nothing then
'not there
else
testitem.visible = true
end if

You could put this into a function to make it cleaner, but it kind of seems like
overkill to me in this situation.

ps.

Debra Dalgleish has lots of info about pivottables and programming here:
http://contextures.com/tiptech.html
Look for pivottables (it's an index)

 

[Martin]

Great, I add the "On error resume next" and "On Error GoTo 0" and that work
!!! Tks you very much for your help ! I also went into the site you mention
and their is real good info out there.

Have a nice day !