VBAでピポットテーブルを作成したいのですが・・・・

質問

VBAを使いピボットテーブルを自動的に再作成（データが追加更新されていく為）したいのですがうまくいきません、何方か良い方法を教えて下さい、参考URLでも構いません、宜しくお願い致します。

回答No.2

OtenkiAme

こんにちは。

ブック内のワークシートにデータがあり、
ピボットテーブルを作り変えずに、範囲だけを更新するのなら
数式を使った名前定義を利用されては如何でしょうか。
VBAを使わなくてもいいですよ。

データリストのシートを表示させ、挿入> 名前定義（Ctrl+F3）で
名前を(仮に) "Database" とし、参照範囲に
=Sheet1!$A$1:INDEX(Sheet1!$H:$H,COUNTA(Sheet1!$B:$B))
のように定義して追加します。式の意味は、
=シート名!参照データの左上セル:INDEX(シート名!参照データの最終列,COUNTA(シート名!空白セルのない列)）

です。
既存のピボットテーブルウィザード2/3まで戻って
[F3]キーを押し、作成した名前を選択して[OK]、
ウィザードを[完了]させます。

後は、データシートにデータを追加して
ピボットテーブルの更新ボタンをクリックするだけです。

どうしてもVBAを使わないといけないのであれば、
次のような方法ではどうでしょうか？
（zap35さんとダブりますが…）
最初に、
1)シート上のリスト内にアクティブセルを置いて、[Ctrl]+[Shift]+[*]
マウスで名前ボックスをクリックして"Database"と名前定義します。
（データ > フォーム の機能を使っているなら他の名前にして下さい。)

2)ピボットテーブルを作成します。
　ウィザード２の範囲選択テキストボックスで"Database"を設定します。
（XL2003までなら自動で取り込まれていると思いますが…）

VBAで更新する時は、
1)シート上のデータ範囲に"Database"と名前定義を再設定する。
2)ピボットテーブルを更新する。
という処理にします。これらの処理をコード化すれば、
可変範囲のピボットテーブル処理ができます。

シート名等変更して下さい。
Sub 名前定義を使ってピボットを更新()
　　Dim myPivot As PivotTable
　　Dim myDataSource As Range
　　With ThisWorkbook
　　　　'データのあるシート
　　　　Set myDataSource = .Worksheets("hoge1") _
　　　　　　　　　　　　.Range("A1").CurrentRegion
　　　　'ピボットテーブルのあるシート
　　　　Set myPivot = .Worksheets("hoge2").PivotTables(1)
　　End With
　　'名前定義
　　myDataSource.Name = "Database"
　　'更新
　　myPivot.RefreshTable
　　Set myPivot = Nothing
　　Set myDataSource = Nothing
End Sub

なお、名前定義の"Database"のことは、
PivotTableWizardメソッドのヘルプに載っています。
XL2002以降では、xlPivotTableSourcetypeに書いてあります。

回答No.1

zap35

ピボットテーブルを作成するデータ範囲に「名前」を定義し、データが追加されたらマクロで「名前」のデータ範囲を変更し、ピボットテーブルをRefreshする方法で考えました。

まず「挿入」→「名前」→「定義」でピボットテーブルにするデータ範囲に名前をつけます。これを仮に　hani　とします。
ピボットテーブルウィザードでデータの範囲は　=hani　として、ピボットテーブルを作成して下さい

次に以下のマクロをATL＋F11でVBE画面を開き、左上の「VPAprojectのシート名右クリック」→「挿入」→「標準モジュール」で開く画面に貼り付けます。その上で実際のシートに合わせて４箇所の修正を行ってください（修正箇所が多くてすみません）

　２行目：ピボットテーブルがあるシート名(Sheet4→xxxx)
　　　（ただしシート内にピボットテーブルは１つだけにする）
　３行目：もとのデータがあるシート名(Sheet1→yyyy)
　４行目：ピボットテーブルのデータ範囲の「名前」(hani→zzzz)
　８行目：ピボットテーブルのデータ範囲("!$A$1:$B$")
　　もしタイトルを含む実際のデータ範囲がB列～G列なら　"!$B$1:$G$"　と記述する
　　$1　はシートの１行目からタイトル行が始まるという意味です

Sub Macro3()
Const sht As String = "Sheet4" 'ピボットTableがあるシート名を記述する
Const sht2 As String = "Sheet1" 'ピボットTableの元データシート名を記述する
Const ar As String = "hani" 'ピボットTableの範囲として定義した「名前」を記述
Dim lastR As Long
　lastR = Sheets(sht).Range("A65536").End(xlUp).Row
　ActiveWorkbook.Names.Add Name:=ar, RefersToLocal:="=" & sht2　_
　　　 & "!$A$1:$B$" & lastR　’この行も実際のデータ列に修正する
　Sheets(sht).PivotTables(1).PivotCache.Refresh
End Sub

最後にVBE画面を閉じて、ワークシート画面に戻りALT＋F8でマクロ一覧を開きマクロ名を選択して「実行」してみてください。データを追加すれば、追加した行もピボットテーブルに計算されます