[[20110502001238]]　『ピボット更新のマクロ』（suzu_miyu）　ページの最後に飛ぶ

『ピボット更新のマクロ』（suzu_miyu）
過去に質問があったかもしれませんが、どうぞ教えてくださいm(__)m

ピボット更新で、元データが変わっても
『「店舗」列の中にある、「一覧」以外のものはすべて選択』
とさせたいです。
記憶マクロを組んで作ったのですが、元データを変えるとうまく起動してくれません。

マクロは以下のようになってます。

    ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotCache.Refresh
    With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("店舗")
        .PivotItems("一覧").Visible = False

どうぞ、わかりましたら教えていただければと思います。
エクセルは2007を使用しています。
よろしくお願いします。

 >元データを変えるとうまく起動してくれません。

 １．上手く起動しないとは、どうなっちゃうのですか？

 ２．どう云う風に変えたのですか？
 　　値を変更したのですか？
 　　データを追加したのですか？
 　（この場合、新範囲がカバーされる様な指定になっていますか？）

 (半平太) 2011/05/02 10:48

早速ご確認でコメントいただいてありがとうございます。
そして説明不足で申し訳ありません。

１．うまく起動しないとは
→ピボットの元データを変えると、ピボットの更新はされるのですが、マクロを組んだ際に含まれていた言葉は選択され、含まれていない言葉（新しく上書きした言葉）は、選択されない状態です。
本当は「一覧」という言葉以外はすべて選択させたいのです。

２．どういう風に変えたのですか？新範囲がカバーされるような指定になってますか？
→元データの「フィールド名」は変更しないまま、値（店舗名）を変更しています。範囲はカバーされるような指定になってます。

伝わりづらいでしょうか・・・。

 >>１．うまく起動しないとは →
 >ピボットの元データを変えると、ピボットの更新はされるのですが、
 >マクロを組んだ際に含まれていた言葉は選択され、
 >含まれていない言葉（新しく上書きした言葉）は、選択されない状態です。
 >本当は「一覧」という言葉以外はすべて選択させたいのです。

 なるほどです。テストしたら実行時エラー1004が出ました。

 中身を見ると、上書きされて消滅したはずの店舗名が、どう云う訳か
 PivotItemsに残っており、その店舗名のVisibleプロパティを操作しようとすると、
 そんなものはもう無いよと云う(詐欺みたいな）状態になる様です。

 バグっぽいです。
 以下、取りあえず、On Error Resume Next で逃げる案

 Sub pvtRefresh()

 Dim pvtItm As Object

     ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotCache.Refresh

     With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("店舗")
         For Each pvtItm In .PivotItems
             On Error Resume Next
                 If pvtItm.Name = "一覧" Then
                     pvtItm.Visible = False
                 Else
                       pvtItm.Visible = True
                 End If
             On Error GoTo 0
         Next
     End With

 End Sub

 (半平太) 2011/05/03 08:20

すごいです★完璧にマクロが動作できました。ばっちりです。
ありがとうございます！！

しかし、バクっぽいんですね＞＜

すみません、先日ご返信いただいて、このマクロでばっちり動作したのですが、
起動時間（マクロが処理する時間）がものすごく長くなってしまって、何か原因は考えられますか？
もしわかりましたら教えてください。度々で申し訳ありませんが、よろしくお願いします。

(suzu_miyu) 2011/5/13 23:08

 こんにちは。

 XL2003までは、半平太さんの例のように一つずつ Visible = False/True を設
 定していくか、Sendkeysを使って“すべて表示”にチェックを入れるしかなかっ
 たのですが、一つずつ処理をしていくと、その都度更新されますから集計内容
 によっては時間がかかることがあります。
 XL2007以降では、ピボットフィールドオブジェクトに対して .ClearAllFilters
 メソッドが用意され、“すべて選択”ができるようになっていますので、使って
 みたら如何でしょうか？

 Sub Sample()
 If Val(Application.Version) < 12 Then Exit Sub
 With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("店舗")
     Select Case .Orientation
         Case xlDataField, xlHidden, xlPageField
             Exit Sub
         Case Else
     End Select
     .ClearAllFilters
     .PivotItems("一覧").Visible = False
 End With
 End Sub

 (OtenkiAme)

OtenkiAmeさん

こんにちわ！
これでやってみましたら、倍以上の速さになりました！！
本当にありがとうございますm(__)m
御礼申し上げます！！

suzu_miyu

コメント返信：

[ 一覧(最新更新順) ]

[[20110502001238]] 『ピボット更新のマクロ』（suzu_miyu） ページの最後に飛ぶ

[[20110502001238]]　『ピボット更新のマクロ』（suzu_miyu）　ページの最後に飛ぶ