集計表をマクロで作成したい。

質問

月末在庫を社内のシステムから抽出するとエクセルには
次のように表示されます。

　Ａ列　　Ｂ列　Ｃ列　　Ｄ列　　Ｅ列　Ｆ列　　　Ｇ列
1(空欄)　AQ73　(空欄)　白桃　　31A　 埼玉県　　　3
2　　　　AQ74　　　　　オレンジ21B　　宮城県Ｂ　1
3　　　　AQ74　　　　　オレンジ31A　 埼玉県　　　1
4　　　　AQ75　　　　　メロン　11A　　北海道　　8
5　　　　AN92　　　　　ラムネ　31A　　埼玉県　　240
6　　　　AP28　　　　　グレープMYN　　埼玉工場　15

下一桁が「A」か「B」のものは倉庫の在庫で「A」は通常品
「B」は出荷止め品です。

この情報から次のような表を作成するマクロを作りたいと思っています。

倉庫コード・名称 11A 31A　　MYN　　　Ａ　 21B　　　B 合計
商品コード 商品名称 北海道 埼玉県 埼玉工場 小計 宮城県Ｂ 小計
AQ73　　白桃　　　　　　　3　　　　 　　　3　　　　　　　　　3
AQ74　　オレンジ　　　　　1　　　 　　　　1　　1　　　　1 2
AQ75　　メロン 8　　　　　　　 　　　 8 8
AN92　　ラムネ　　　　　　240　 240 240
AP28　　グレープ　　　　　　　　 15 15 15
　合　計　　　　　　8　　　244　 15　　　267　1　　　　 1 268

Ａ列に商品コード・Ｂ列に商品名称・Ｃ列から「通常品と小計」「出荷止め品と小計」「合計」というような表をマクロで作りたいのです。

マクロの記録をしようとしても、どうやってピボットテーブルを設定
したらよいのか思いつかず、うまく行きません。

アドバイスいただけませんか？

回答No.1

mt2008

ピボットテーブルはあまりマクロでは作りません。
予め作っておいてデータの更新をマクロで行う場合が殆どではないかと思います。

参考までに、お望みの形に近いピボットテーブルを作って見ました

まずは元データの整形。添付の図を見てください。各列の先頭に項目名を付けました。
また、空列があるとピボットでは上手く扱えないので、C列には項目名だけ「空欄」と付けました。また、通常品と出荷止め品の区別が付ける項目「状態」をH列に作りました。

ピボットで、データ範囲をB1:H7
ピボットテーブルレイアウトで、行に商品コード、商品名称（この順番で）
列に、状態、倉庫コード、倉庫　（この順番で）
データに　合計/DATA

さらにレイアウト画面で行に配置された商品コードをWクリック、集計：なし
列に配置された倉庫コードをWクリック、集計：なし

これで如何でしょうか。

補足

ピボットテーブルを作成して更新したら良いのですね！！
新しい発見です。

一度、ピボットテーブルを作りました。
そこに、月を更新した場合のデータを作成して、
ピボットテーブルの更新前のデータに貼り付けて、
ピボットテーブル上で更新をしました。

そしたら、特定の行も列もがデータが入っているのにも関わらず
隠れてしまいました。この現象は、ピボットテーブルの設定に原因があるのでしょうか。
これを全て表示させて(空白)を消去するのを手作業では
できるのですが、マクロで自動化することはできないでしょうか？