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

質問

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

回答No.4

Xen · ベストアンサー

#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

お礼

色々試したのですが出来ませんでした。いろいろ教えていただき本当にありがとうございました。

回答No.3

Wendy02

こんにちは。

私のコードの場合、以下の二点だけです。

　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

と加えてください。

これだけでよいはずです。
これで、もし分らないようですと、ちょっと厳しいですね。

お礼

色々試したのですが出来ませんでした。いろいろ教えていただき本当にありがとうございました。

回答No.2

Wendy02

こんばんは。

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

補足

せっかくご回答いただいたのに無知の為出来ませんでした；；お時間がある時で構いませんので対処方法を教えてください。シートの名前が違うのは'9月分計算結果データ'の部分が毎月変わります。

'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'9月分計算結果データ'!R1C1:R500C164").CreatePivotTable TableDestination:="", _
TableName:="ピボットテーブル1", DefaultVersion:=xlPivotTableVersion10

回答No.1

Xen

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

マクロであればビボットの作成部分に「"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")」でシート名を実行年月に変更出来ます。

補足

せっかくご回答いただいたのに無知の為出来ませんでした；；お時間がある時で構いませんので対処方法を教えてください。シートの名前が違うのは'9月分計算結果データ'の部分が毎月変わります。

'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'9月分計算結果データ'!R1C1:R500C164").CreatePivotTable TableDestination:="", _
TableName:="ピボットテーブル1", DefaultVersion:=xlPivotTableVersion10