現在、仕事の関係でエクセルを多用しており、
仕事を楽にするためマクロを勉強しています。
インターネットを駆使して、下記のコードを作ってみたのですが、
【】の範囲で「参照が正しくありません」とエラーが出てしまいます。
どこがおかしいかご教授いただけますでしょうか?
自動記録でベースを作った後、範囲の指定を
VBA上で行ったつもりです。
データの集計は「A」シートのA~G列まで、行は不定なので、
「A」シートの一番下までのデータを集計するようにしたいです。
縦に「要素」、内容に「金額」を集計するよう、
ピボットを記録しています。
Sub ピボットを行う()
'
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
【 ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"A!Selection.Address").CreatePivotTable TableDestination:="", TableName:= _
"ピボットテーブル1"】
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("ピボットテーブル1").SmallGrid = False
ActiveSheet.PivotTables("ピボットテーブル1").AddFields RowFields:="要素"
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("金額")
.Orientation = xlDataField
.Caption = "合計 : 金額"
.Function = xlSum
End With
End Sub
よろしくお願いいたします。
No.1ベストアンサー
- 回答日時:
こんにちは。
>(SourceType:=xlDatabase, SourceData:="A!Selection.Address")
ここを
(SourceType:=xlDatabase, SourceData:= Selection.Address(external:=True))
と変更すればできます。
ついでに、SelectやActivateは選択状況に依存し、不安定です。
ActiveWorkbookは仕方ないのかもしれませんが
Sub sample()
Dim r As Range
With ActiveWorkbook
With .Sheets("A")
Set r = .Range("G1", .Range("A1").End(xlDown))
End With
With .PivotCaches.Add(SourceType:=xlDatabase, SourceData:=r.Address(external:=True))
With .CreatePivotTable(TableDestination:="")
.AddFields RowFields:="要素"
With .PivotFields("金額")
.Orientation = xlDataField
.Caption = "合計 : 金額"
.Function = xlSum
End With
End With
End With
End With
Set r = Nothing
End Sub
などとしたほうが良いかも^ ^
QNo.2097393も参考にしてみてください。
ご回答、ありがとうございました。
教えて頂いたマクロで出来るようになりました。大変助かります。
後学のため、コメントを載せようと思っているのですが、
頂いたマクロにて不明点がいくつかあります。
(下記コメントの(1)~(4)です)
いつでも結構ですので、ご返答頂けないでしょうか?
(分かる範囲で自分で記載しています)
Sub sample()
Dim r As Range '(1)この式は何のためですか??
With ActiveWorkbook
With .Sheets("A") 'Aシートを選ぶ
Set r = .Range("G1", .Range("A1").End(xlDown)) 'G1からA1の最下層までを選択
End With
With .PivotCaches.Add(SourceType:=xlDatabase, SourceData:=r.Address(external:=True)) '(2)ピボットの範囲はドコに記入されているのですか??
With .CreatePivotTable(TableDestination:="")
.AddFields RowFields:="要素" 'ピボットの横列に要素を選択
With .PivotFields("金額") 'ピボットの内容に金額を選択
.Orientation = xlDataField
.Caption = "合計 : 金額" '金額を合計にする
.Function = xlSum
End With '(3)End Withが四つも並ぶのはなぜですか?
End With
End With
End With
Set r = Nothing '(4)この式の意味が分かりません
End Sub
仕事で忙しい中の独学だと限界があるようです。。。
初歩的な質問かも知れませんが、ご回答頂ければ幸いです。
No.3
- 回答日時:
■(2)について
>(2)ピボットの範囲はドコに記入されているのですか??
ピボットテーブルを作成する先。という意味ですよね。
答えは...『省略しています』
.CreatePivotTable(TableDestination:="")
この TableDestination で指定するのですが、上記のように省略すると
ピボットウィザード3/3で[新規ワークシート]にピボットを作成するのと同じ事になります。
指定することも可能です。
Sub sample2()ではあえて、
ActiveWorkbook.Sheets.Add
とシートを事前に追加し、(追加したシートは必ずActiveSheetになるので)
TableDestination:=ActiveWorkbook.ActiveSheet.Range("E5")
と指定してみました。
また、お使いのバージョンはxl2000だと思いますが、
xl2002以降だと、ピボットの元データ範囲の指定は
SourceData:=r
や
SourceData:=Range("G1", .Range("A1").End(xlDown))
というように、セル範囲を表すObjectで指定できます。
xl2000だと
r.Address(external:=True)というセル範囲を表すアドレス『文字列』で指定しないといけません。
これに対して作成先は
TableDestination:=Range("E5") などとセル範囲での指定です。
■もう一点。
一般機能の[名前の定義]を使ってピボット元データの範囲を可変にする事もできます。
Sheets("A")で[Ctrl]+[F3]キーで[名前の定義]ウィンドウが開きます。
[名前]欄に database (例えば)
[参照範囲]欄に =OFFSET($A$1,0,0,COUNTA($A:$A),7)
[OK]
として、手作業でピボットを作成します。
ピボットウィザードの元データの参照範囲に database
とすると、毎回ピボットを作成するのではなくて
一度作ったピボットの[データの更新]をするだけで良いです。
■蛇足として【VBAの理解を深めるには】
1)意味がわからない語句などは、その語にキャレット(マウスカーソル)をあてて[F1]キー押下で
HELPトピックにアクセスできます。(できない語句もあります)
2)全体を通してコードの動きを確認したい時は、VBEウィンドウを半画面の状態で、
コード内で[F8]キー押下で1ステップずつ、Excel画面の動きも見ながら実行すると効果的です。
3)その時、[ローカルウィンドウ]を表示させておくと、変数の内容も確認できます。
>仕事で忙しい中の独学だと限界があるようです。。。
私も同じような環境でした^ ^
>仕事を楽にするため
これが何よりも上達の原動力になると思います。
では。がんばってください^ ^
No.2
- 回答日時:
こんにちは。
■(1)(4)について
Dim r As Range
で、Object型(Range)の『変数』として r を準備しています。
Set r = .Range("G1", .Range("A1").End(xlDown))
でその r にセル範囲を一旦、代入して、その後SourceData指定で使用します。
通常 SetしたObject型の変数領域は、そのプロシージャが終了すると
自動的にリセットされるとされていますが、
Set r = Nothing
で、明示的にそのObjectを破棄して後始末をしています。
『変数』というのは、容れモノ、というか、別名、というか。そんなようなものです。
実行効率や、可読性、メンテナンス性を良くするためなどに使います。
私の拙い説明より、
"VBA" "変数について"
でgoogle検索してみてください。そこから
http://oshiete1.goo.ne.jp/kotaeru.php3?q=2148713
http://pc.nikkeibp.co.jp/article/NPC/20070208/26 …
などを参考にされると良いですよ。
■(3)について
実行効率や可読性を良くするという意味ではWithステートメントも同じです。
あるObjectに対して処理する時、何度も呼び出さず、With ステートメントで一度だけ指定して呼び出し、
効率を上げるためにまとめるわけです。
前述のコードを簡単に階層化させると
┌With ActiveWorkbook
├─この間 .の前は ActiveWorkbook.を指す
│┌With .PivotCaches
│├─この間 .の前は ActiveWorkbook.PivotCaches.を指す
││┌With .CreatePivotTable
││├─この間 .の前は ActiveWorkbook.PivotCaches.CreatePivotTable.を指す
│││┌With .PivotFields("金額")
│││├─この間 .の前は ActiveWorkbook.PivotCaches.CreatePivotTable.PivotFields("金額").を指す
│││└End With
││└End With
│└End With
└End With
...な感じです。
前述コードを、むりやりWith ステートメントを使わず書くなら
Sub sample2()
ActiveWorkbook.Sheets.Add
ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:="A!" & ActiveWorkbook.Sheets("A").Range( _
"G1", ActiveWorkbook.Sheets("A").Range("A1").End(xlDown)).Address _
).CreatePivotTable TableDestination:=ActiveWorkbook.ActiveSheet.Range("E5")
ActiveWorkbook.ActiveSheet.PivotTables(1).AddFields RowFields:="要素"
ActiveWorkbook.ActiveSheet.PivotTables(1).PivotFields("金額").Orientation = xlDataField
ActiveWorkbook.ActiveSheet.PivotTables(1).DataFields(1).Caption = "金額計"
ActiveWorkbook.ActiveSheet.PivotTables(1).DataFields(1).Function = xlSum
End Sub
と書けます。
『変数』『Withステートメント』については
『Excel特有のオブジェクト、メソッド、プロパティの理解』
http://www.asahi-net.or.jp/~ef2o-inue/vba_k/sub0 …
この辺りが参考になるかと^ ^
ご返信ありがとうございました。
詳しいご説明で大変分かりやすかったです。
DimとかWithはいろんなところで使えそうですね。
まだまだ勉強中なので、これからココで質問することもあると思いますが、そのときはまたお付き合い頂ければ幸いです。
今回は本当に助かりました。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) データのある範囲を選択するVBAについて 2 2022/09/03 00:20
- Visual Basic(VBA) マクロで最終行を取得してコピーしたい 3 2022/04/06 19:07
- Excel(エクセル) excel ピポットテーブルの更新について 1 2022/05/13 16:12
- Visual Basic(VBA) ExcelVBAのマクロについて。 9 2022/05/04 14:50
- Excel(エクセル) 日付で矢印マクロ 4 2023/07/25 16:47
- Visual Basic(VBA) まとめシートから集計シートへA列のコードが一致したら1行コピーするマクロをネット上で見つけました。こ 1 2022/08/30 14:11
- Excel(エクセル) エクセルで最下行にデータを追加するVBA 6 2023/05/09 09:30
- Excel(エクセル) エクセルのVBAにショートカットキーの割り当て 3 2022/07/13 14:19
- Visual Basic(VBA) エクセル VBA 処理スピードを上げたいのですが。 6 2023/03/31 20:52
- Excel(エクセル) 【マクロ】スクショ印刷がうまく動かない件 5 2022/12/06 17:37
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Vba SelStart、SelLen教えてく...
-
現在のブックを閉じないで、マ...
-
ExcelVBA シート名を複数セルか...
-
ユーザーフォームに別シートか...
-
【VBA】マクロの入ったファイル...
-
VBA listBoxから
-
VBAコンボボックスで選択した値...
-
VBA初心者 Ctrl+での操作、ボタ...
-
VBA実行後に元のセルに戻りたい
-
Excel-VBAのmsgBox()の不思議
-
エクセルのマクロについて教え...
-
FileCopy時のエラー
-
Excelのマクロでワードのテキス...
-
【ExcelVBA】インデックスが有...
-
Outlookの「受信日時」「送信者...
-
VBAで各列の"+"と"o"の合計数を...
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
VBAのコードを教えてください
-
【ExcelVBA】インデックスが有...
-
ExcelVBA シート名を複数セルか...
-
エクセルvbaについて
-
エクセルのマクロについて教え...
-
【VBA】マクロの入ったファイル...
-
VBA UserFormからの転記で
-
エクセルVBAの配列について
-
Excelで「Ctrl+c」、「Ctrl+v...
-
VBAコードについて教えてくださ...
-
ExcelのVBAコードについて教え...
-
Excel マクロについての相談
-
VBAで質問があります
-
VBAコードについて
-
【ExcelVBA】VBA実行でダイアロ...
-
Excel関数またはVBAでの質問に...
-
ExcelのVBAコードについて教え...
-
ExcelのVBAコードについて教え...
-
ExcelのVBAコードについて教え...
-
Outlookの「受信日時」「件名」...
おすすめ情報