現在、仕事の関係でエクセルを多用しており、
仕事を楽にするためマクロを勉強しています。
インターネットを駆使して、下記のコードを作ってみたのですが、
【】の範囲で「参照が正しくありません」とエラーが出てしまいます。
どこがおかしいかご教授いただけますでしょうか?
自動記録でベースを作った後、範囲の指定を
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で質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Web画面の文字をVB6で取得したい
-
Excel 範囲指定スクショについ...
-
配列のペースト出力結果の書式...
-
VBA 別ブックから条件に合うも...
-
エクセルVBAにて =A1=B1とすれ...
-
Excelについて
-
【ExcelVBA】インデックスが有...
-
Excelで画像URLを1つずつセル...
-
Excel VBA 文字列のセルを反映...
-
VBA 指定した回数分、別シート...
-
【VBA】カーソルのある行の1行...
-
ExcelVBAのFindFirstエラ...
-
VBA 入力箇所指定方法
-
VBA 複数の各シートに行を追加...
-
10行目にフィルターを使用して...
-
Excel VBAで値を変えながら、pd...
-
VBA実行後に元のセルに戻りたい
-
エクセルのマクロについて教え...
-
vbaにてseleniumを使用したedge...
-
メールの件名をデコードしたい
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
VBA 別ブックからコピペしたい...
-
Vba ファイル書き込み時に書き...
-
Excel_VBAについて質疑です。(...
-
VBAの間違い教えて下さい
-
VBA コードどこがおかしいですか?
-
VBA 円グラフ 特定条件に一致し...
-
VBA 別ブックから条件に合うも...
-
pdfファイルの複数添付 引数の型
-
【ExcelVBA】インデックスが有...
-
ExcelVBAマクロで実行した時の疑問
-
Vba UserformからExcelシートの...
-
VBA初心者です。次のVBAコード...
-
Outlookの「受信日時」「件名」...
-
Excel 範囲指定スクショについ...
-
vbs ブック共有を解除
-
配列のペースト出力結果の書式...
-
Excel VBAで値を変えながら、pd...
-
VB.net(VB)で、フォームにExcel...
-
vbaにてseleniumを使用したedge...
-
ExcelVBA シート名を複数セルか...
おすすめ情報