ピボットテーブルの集計セルをダブルクリックすると、ドリルダウンされ、詳

質問

ピボットテーブルの集計セルをダブルクリックすると、ドリルダウンされ、詳細データが、sheet#に出力されます。何度も行うとシートが増えてしまいますが、同じシートに結果を上書きして出力することはできませんか？

他サイトで解決法として以下の対策が挙げられていましたが、うまく作動しませんでしたので
他のやり方があればと思い質問させて頂きます。

<他サイト解決法>
1.ピボットテーブルを作成後、画面下部のシート名を右クリックし、コードの表示を選択
2.Microsoft Visual Basicの画面が表示されるので↓のコードをそのまま貼り付け

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.DisplayAlerts = False
Sheets(Sheets.Count - 1).Delete
Selection.ShowDetail = True
Application.DisplayAlerts = True
End Sub

3. Microsoft Visual Basicの画面を閉じる
4.ピボットテーブルのシートを表示し、集計セルをダブルクリック
5.別シートに結果が表示される（このときシートの数は、ピボットテーブルのシートと合わせて2つ以上ある必要があります）
6.もう一度、集計セルをダブルクリックすると古い方の集計結果シートが削除され、新しいシートが新規で作成されます。（結果的にシートは増えていかない）

=======ここまで=================================================================
結局、シートが増え続けている結果となりました。
集計セルをダブルクリックして新規のシートに詳細データが表示される仕様みたいですが
なるべくマクロを組まない方法があれば尚良いです。

宜しくお願い致します。

回答No.1

end-u · ベストアンサー

マクロを組まないと無理です。
また、詳細データを表示するシートは常に１枚、という仕様で良いですか？
それとも集計項目が違えば別シートに詳細データを表示する仕様でしょうか？

『詳細データを表示するシートは常に１枚、という仕様』の場合、
該当シートのシートモジュールに以下のコードを置きます。

'SheetModule
Option Explicit
'-------------------------------------------------
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
　　　　　　　　　　　　　　　　　　　　Cancel As Boolean)
　　If Me.PivotTables.Count = 0 Then Exit Sub
　　Dim r As Range
　　On Error Resume Next
　　Set r = Intersect(Target, Target.PivotTable.DataBodyRange)
　　On Error GoTo 0
　　If Not r Is Nothing Then
　　　　Application.OnTime Now, Me.CodeName & ".test1"
　　　　Set r = Nothing
　　End If
End Sub
'-------------------------------------------------
Private Sub test1()
　　Const sName = "pvtDetail"
　　On Error Resume Next
　　Application.DisplayAlerts = False
　　Sheets(sName).Delete
　　Application.DisplayAlerts = True
　　On Error GoTo 0
　　ActiveSheet.Name = sName
End Sub
'-------------------------------------------------
詳細データ表示用のシート名を予め決めておきます。
上の例では"pvtDetail"。
上書きというよりも、既存"pvtDetail"を削除して新規作成、という感じです。

お礼

詳細データを表示するシートは常に１枚で行うことができました。
ありがとうございました。