ピポットテーブルをマクロ登録

Question

マクロ初心者ですよろしくお願いします。
毎月システムからデータを切り出し、ピポットテーブルで表を作成しております。
毎月同じピポットを作っているのでマクロ登録をしたいのですが、他のシート（ブック）で使用するとエラーが出てしまいます。
シート（ブック）の名前が違ってもマクロを使用する方法はありませんでしょうか？
質問の意味がわかりにくいかもしれませんがよろしくお願いします。

Xen · Accepted Answer

#1です。
Wendy02さんが汎用的なマクロを詳しく説明されているので不要かもしれませんが。

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'9月分計算結果データ'!R1C1:R500C164").CreatePivotTable TableDestination:="", _
TableName:="ピボットテーブル1", DefaultVersion:=xlPivotTableVersion10

ここだけの内容で見ると、
"'9月分計算結果データ'!R1C1:R500C164"
を
"R1C1:R500C164"
にするとアクティブなシートのデータが使われます。

後は、
"ピボットテーブル1"
を
ActiveSheet.Name
にするとこのマクロで同一ブック内に複数ピボットを作成しても問題ないかと。

で、今までの内容を合わせると以下のようになります。

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"R1C1:R500C164").CreatePivotTable TableDestination:="", _
TableName:=ActiveSheet.Name, DefaultVersion:=xlPivotTableVersion10

ただ、気になるのは
"R1C1:R500C164"
のデータ範囲が常に固定かということです。

可変の場合でタイトル行には何らかの文字が入力されているのなら以下になります。

sub (マクロ名)
　　Range("A1").Select
　　Range(Selection, Selection.End(xlDown)).Select
　　Range(Selection, Selection.End(xlToRight)).Select
　　（何か処理があれば）
　　ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
　　Selection.Address).CreatePivotTable TableDestination:="", _
　　TableName:=ActiveSheet.Name, DefaultVersion:=xlPivotTableVersion10
　　（後続処理）
End Sub

Wendy02 · Answer

こんにちは。

私のコードの場合、以下の二点だけです。

　SocBook = ThisWorkbook.Name　'特に書き込む必要ない
　SocSheet = ActiveSheet.Name　'特に書き込む必要ない
　SocRange = Selection.Address(True, True, xlR1C1) 'マウスの選択範囲
　'以下は、ピボットを作る場所
　DestBook = "Testbook2.xls"　'←目的のブック
　DestSheet = "Sheet1"　　'←目的のシート
　DestAdd = Range("A3").Address(True, True, xlR1C1)

1)
シート名の先頭が、数字の場合は、、'　　'で囲まなくてはなりませんので、

　SocSheet = ActiveSheet.Name　'←開けてあるシート
　　↓
　SocSheet = "'" & ActiveSheet.Name & "'"　'←開けてあるシート

としなければなりません。

2)

TableName:="ピボットテーブル1", _
DefaultVersion:=xlPivotTableVersion10 

と加えてください。

これだけでよいはずです。
これで、もし分らないようですと、ちょっと厳しいですね。

Wendy02 · Answer

こんばんは。

VBAを本格的に作る人は、あまり、ピボットテーブルは使いませんが、以下のようにしたらようと思います。
たぶん、ほとんどは、記録マクロで作ったものでしょうから、以下のようにすればよいと思います。

目的のブック・目的のシートに出来上がるはずです。

例:
TestBook2 の Sheet1の A3 から、ピボットテーブルを作る場合。
最初に、範囲をマウスで選択します。

注意:
なお、ここは、半角の時がありますから、必ず、下の行に出てくる名前に合わせてください。
　TableName:="ピボットテーブル１"　'

この目的のブックとかシートとかは、別の場所とか、Userformなどに設定させてもよいです。

'---------------------------------------
Sub SampleMacro1()
Dim SocBook As String　'ソースのブック名
Dim SocSheet As String 'ソースのシート名
Dim SocRange As String 'ソースの範囲アドレス
Dim DestBook As String '目的のブック
Dim DestSheet As String '目的のシート
Dim DestAdd As String ' 目的のアドレス

　SocBook = ThisWorkbook.Name
　SocSheet = ActiveSheet.Name
　SocRange = Selection.Address(True, True, xlR1C1)
　'
　DestBook = "Testbook2.xls"
　DestSheet = "Sheet1"
　DestAdd = Range("A3").Address(True, True, xlR1C1)
　
　If Selection.Cells.Count = 1 Then MsgBox "範囲が選択されていません": Exit Sub
　　Workbooks(DestBook).Activate
　　
　　Workbooks(DestBook).PivotCaches.Add( _
　　　　SourceType:=xlDatabase, _
　　　　SourceData:="[" & SocBook & "]" & SocSheet & "!" & SocRange).CreatePivotTable _
　　　　TableDestination:="[" & DestBook & "]" & DestSheet & "!" & DestAdd, _
　　　　TableName:="ピボットテーブル１"　'←注意:下の行に出てくるピボットテーブル名に合わせてください。

この下の２行 は、コメントにします。
'　　ActiveSheet.PivotTableWizard _
　　　　 TableDestination:=ActiveSheet.Cells(3, 1)
　'　 ActiveSheet.Cells(3, 1).Select
'後は、そのまま使用します。
　　・
　　・
　　・
End Sub

Xen · Answer

ビボットテーブル作成時にマクロの記録を使用して作成したのでしょうか？

マクロであればビボットの作成部分に「"Sheet1!R1C1:R4C2"」のような記述があるはずです。
この場合Sheet1でしか動作しないため、シートを固定したくない場合は「Sheet1!」を外して下さい。
データ表が固定である場合はこれで動作するはずです。

データ表が（列数は同じとして）可変の場合は以下のいずれかの対応をします。
(1)データ表の最右列に空白がある場合
　「"Sheet1!R1C1:R4C2"」のように記述されている箇所を「Selection.Address」に変更し
　マクロ実行前に手動でデータ表全体を選択します。

(2)データ表の最右列に空白がない場合（列タイトルは必ずありますよね？）
　「"Sheet1!R1C1:R4C2"」のように記述されている箇所を「Selection.Address」に変更し
　以下のマクロを「Sub xxxxxx（マクロ名）」の直後に入れます。
　　Range("A1").Select　　注意：A1には実際のデータ表の右上のセルを指定します。
　　Range(Selection, Selection.End(xlDown)).Select
　　Range(Selection, Selection.End(xlToRight)).Select


マクロを実行して作成するビボットテーブルをブック内に複数保持したい場合は、以下の対応も必要です。
ピボットテーブルの名前を指定している「"ピボットテーブル1"」のような記述を例えば「Format(Now,"yyyymm")」
に変更します。これは名前の重複を防ぐためです。
また、併せてシート名も変更しておくと分かり易いかもしれません。
例えば「ActiveSheet.Name = Format(Now, "yyyymm")」でシート名を実行年月に変更出来ます。

ピポットテーブルをマクロ登録

#1です。

こんにちは。

こんばんは。

この回答への補足

ビボットテーブル作成時にマクロの記録を使用して作成したのでしょうか？

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング