ピポットをマクロに自動登録しました。ところが作動するとデバックが起きてしまいます。VBAに特別なプログラムを付け加える必要があるのでしょうか? 初心者なので勉強不足のところが多いのですが、どうかよろしくお願いします。
なお、シートは3枚で「data」「number」「answer」があり、「data」には価格の情報がないので、「number」シートからvlookupで情報を拾ってきています。ピポットは「answer」シートに作り、値の貼り付けをしてセルA1の列幅を20にしています。
Range("D1").Select
ActiveCell.FormulaR1C1 = "担当"
ActiveCell.Characters(1, 2).PhoneticCharacters = "タントウ"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C1:R92C1,number!R2C1:R109C2,2,0)"
Selection.AutoFill Destination:=Range("D2:D92"), Type:=xlFillDefault
Range("D2:D92").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A2").Select
Application.CutCopyMode = False
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"data!R1C1:R92C4").CreatePivotTable TableDestination:="[集計(1).xls]answer!R1C1" _
, TableName:="ピボットテーブル1", DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("性別")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("商品番号")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("担当")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("ピボットテーブル1").PivotSelect "商品番号[All]", xlLabelOnly, _
True
Range("B2").Select
ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("商品番号").Orientation = _
xlHidden
ActiveSheet.PivotTables("ピボットテーブル1").AddDataField ActiveSheet.PivotTables( _
"ピボットテーブル1").PivotFields("価格"), "合計 / 価格", xlSum
Columns("A:A").Select
Selection.ColumnWidth = 20
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
No.3ベストアンサー
- 回答日時:
こんにちは。
作った集計表を数値化していますが、
TableName:="ピボットテーブル1" のピボットテーブルが存在するとか、TableDestination:="[集計(1).xls]answer!R1C1"のブック名が異なっているとか、確認してみてください。
省略できる引数もあるので、ヘルプファイルを活用して、分からないコードは調べるクセをつけるといいですよ。
作成先のシートを指定している場合は、
一旦、シートのセルをクリアしてから作成するのが常套手段だと思います。
また、複数のピボットテーブルを操作するのでなければ、
名前を省略して、INDEXで指定してあげると処理がすっきりします。
また、ピボットテーブルの作成は、手作業での手順が把握できたら、
ウィザード3/3のレイアウトで組むようにするといいです。
ワークシートで組むのと違ってすっきりしたコードが書けます。
記録したコードの編集ですが、
ほとんど場合、□.Select、Selection.△と続く処理は、□.△というようにつないで書くことができます。画面のちらつきもなくなりますし、
処理が早くなります。ぜひコードの編集にチャレンジしてください。
下に、サンプルを掲示しますので、F8でステップ実行しながら、
murasakishさんの処理の参考にしてみてください。
Sub 例えば()
With Worksheets("data")
.Range("D1").Value = "担当"
With .Range("D2")
.FormulaR1C1 = "=VLOOKUP(R2C1,number!R2C1:R109C2,2,0)"
.AutoFill Destination:=.Parent.Range("D2:D92"), Type:=xlFillDefault
End With
With .Range("D2:D92")
.Copy
.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
Application.Goto Reference:=.Range("A2")
End With
Worksheets("answer").Cells.Clear
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=Worksheets("data").Range("A1").CurrentRegion.Address(External:=True)) _
.CreatePivotTable TableDestination:=Worksheets("answer").Range("A1")
With Worksheets("answer")
.Activate
With .PivotTables(1)
.PivotFields("商品番号").Subtotals(1) = False
.AddFields RowFields:=Array("商品番号", "担当"), ColumnFields:="性別"
.PivotFields("価格").Orientation = xlDataField
With .TableRange1
.Copy
.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
.Interior.ColorIndex = xlNone
Application.Goto Reference:=.Range("A1")
End With
End With
.Columns("A:A").ColumnWidth = 20
End With
End Sub
No.2
- 回答日時:
エラーの内容(エラー番号、メッセージ)とか、状況(例えば1回目は
上手くいくのに2回目は失敗するとか)の説明がないと回答側にはエラー
の原因がつかめません。
なぜなら、コードには問題がなく、シート上のデータ側に問題があるなど
コード上では読み取れない原因があるのかもしれないからです。
そのため、少なくともこのような情報の提供が必要なのです。
で、気になる点を。
> [集計(1).xls]answer!R1C1
という表記はおかしくないですか?単に answer!R1C1 にしてみたら?
自動記録で作ったので可笑しいところには気がつきませんでした。これでも実行されましたので、ここが原因ではなかったようですが…。今後のこともありますので覚えるようにします。ありがとうございました。
No.1
- 回答日時:
> ところが作動するとデバックが起きてしまいます。
どの行でエラーになるのかを書かないと。。それから、どのような状況で
エラーになるのかの説明、Excel や OS のバージョン等も書かないと。。
試してないけど、とりあえず気づいた部分です。
■ [追加] 1行目にシートのアクティブ化コード
Range("D1").Select
ActiveCell.FormulaR1C1 = "担当"
↓
Worksheets("data").Activate
Range("D1").Select
ActiveCell.FormulaR1C1 = "担当"
■ [修正]VLOOKUP 関数の第一引数がヘン
ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C1:R92C1,number!R2C1:R109C2,2,0)"
↓
ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C1,number!R2C1:R109C2,2,0)"
■ [追加] シートのアクティブ化コード
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("性別")
↓
Worksheets("answer").Activate
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("性別")
この回答への補足
Excel2003です。失礼しました。VBA用に書き直しました。デバックが起きるのは※の部分です。
Dim L As Integer
Worksheets("data").Activate
Range("D1").Select
ActiveCell.FormulaR1C1 = "担当"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C1,number!R2C1:R109C2,2,0)"
Selection.AutoFill Destination:=Range("D2:D92"), Type:=xlFillDefault
Range("D2:D92").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A2").Select
Sheets("answer").Select
Range("A1").Select
Worksheets("answer").Activate
※ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"data!R1C1:R92C4").CreatePivotTable TableDestination:="[集計(1).xls]answer!R1C1" _
, TableName:="ピボットテーブル1", DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("性別")
Worksheets("answer").Activate
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("商品番号")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("担当")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("ピボットテーブル1").PivotSelect "商品番号[All]", xlLabelOnly, _
True
Range("B2").Select
ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("商品番号").Orientation = _
xlHidden
ActiveSheet.PivotTables("ピボットテーブル1").AddDataField ActiveSheet.PivotTables( _
"ピボットテーブル1").PivotFields("価格"), "合計 / 価格", xlSum
Columns("A:D").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A21").Select
End Sub
よろしくお願いします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) マクロを短くする 1 2023/01/15 00:11
- Visual Basic(VBA) マクロで最終行を取得してコピーしたい 3 2022/04/06 19:07
- Excel(エクセル) エクセル VBAでシートのコピーを作りたい 1 2023/05/18 07:42
- Visual Basic(VBA) 動きっぱなしです。止め方とプロシージャの間違いを教えて下さい! 5 2022/08/15 23:08
- Excel(エクセル) excel ピポットテーブルの更新について 1 2022/05/13 16:12
- Visual Basic(VBA) データのある範囲を選択するVBAについて 2 2022/09/03 00:20
- Excel(エクセル) エクセルのマクロでコピー後の貼り付け先を毎回指定したところにしたい 5 2022/08/12 10:47
- Visual Basic(VBA) vbaのエラー対応(実行時エラー7:メモリが不足しています) 4 2023/04/24 00:20
- Excel(エクセル) 表示形式、文字列セル(列)に数式を入力するには マクロ 1 2022/09/18 10:53
- Visual Basic(VBA) エクセルのマクロを使ってメールを送る方法について教えてください 2 2022/03/29 01:36
関連するカテゴリからQ&Aを探す
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【VBA】PDF出力に任意のファイ...
-
エクセルでページごとにヘッダ...
-
エクセルで シート保護のパス...
-
[EXCEL] あるフィールドをキー...
-
【VBA】#N/Aを無視して串刺し...
-
Excelマクロ パスワードを入力...
-
エクセルのシートごとに連番を...
-
ロータスについての質問。
-
エクセル データベースからの引用
-
ユーザー定義関数の使用法
-
エクセルVBA ワークシート検索...
-
PowerPointの表内のカンマ
-
LINEのこの空白ってどんな意味...
-
EXCELでタイトル行と一番下の行...
-
名簿の漢字名を関数で半角カナ...
-
Wordの差し込み印刷で空白行が...
-
EXCELで縦の行全てに一括して文...
-
エクセルでオートサムを使った...
-
エクセルでleft関数の結果が表...
-
EXCEL 連動したドロップダウン...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【VBA】PDF出力に任意のファイ...
-
エクセルのシートごとに連番を...
-
エクセルで設定していないのに...
-
エクセルでページごとにヘッダ...
-
excel串刺し計算で合計値が表示...
-
Excelマクロ パスワードを入力...
-
複数のEXCELシートの印刷順の指定
-
既存ワークシートにピボットテ...
-
エクセルで、ハイパーリンクの...
-
社内SEです。機種、ライセンス...
-
エクセルで個人成績グラフをつ...
-
【VBA】#N/Aを無視して串刺し...
-
Access2010 Excelのエクスポー...
-
エクセルのイベントVBAを複数の...
-
この記号、手短(テミジカ)に日本語...
-
[EXCEL] あるフィールドをキー...
-
エクセル VBAでシートのコピー...
-
Excel 一覧表から特定の数値を...
-
excelのシート番号を取得したい...
-
マクロ記録機能を使ってグラフ...
おすすめ情報