Withステートメントの使い方

プログラムの中で単一のシートやセルなど一つの処理内容
に対して一連の処理をする時「With」ステートメントで
ひとくくりで記述できます。
と参考書やwebサイトで見ました。
とりあえず自力で作成した構文は完璧では有りませんが
「With」ステートメントを使えるようになりました。
さて以下の2種類の構文ですがマクロの記録で作成されています。
これを簡素化しようと思ったのですが
すでにWith～End Withが散乱しています。
これはもう纏めるのは無理なのでしょうか？
私が思うに同じ単語が何回も出てくるので
もう少し簡素化できるのではと挑戦しましたが
誤動作になってしまいます。
すでにWith～End Withでくくられていますので
そのくくりはやはり崩すと駄目なのでしょうか？
くくり内の2行→1行にする程度が限度でしょうか？
これはどのようにすれば簡素化できるのか
コツを教えてください。

With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("順番")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("区分")
.Orientation = xlColumnField
.Position = 2
End With
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("区分名")
.Orientation = xlColumnField
.Position = 3
End With
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("品番")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("仕入")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("品名")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("棚番")
.Orientation = xlRowField
.Position = 4
End With

（省略）

With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("品番")
.PivotItems("(空白)").Visible = False
End With
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("仕入")
.PivotItems("(空白)").Visible = False
End With
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("品名")
.PivotItems("(空白)").Visible = False
End With
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("棚番")
.PivotItems("(空白)").Visible = False
End With
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("順番")
.PivotItems("(空白)").Visible = False
End With
ActiveWindow.SmallScroll ToRight:=3
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("親品番")
.PivotItems("(空白)").Visible = False
End With
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("親品名")
.PivotItems("(空白)").Visible = False
End With

No.2 回答者： jcctaira 回答日時： 2011/03/14 15:04

gx9wxさん

こんにちは。

例えば以下のようにすれば多少すっきりするかと思います。
　
With ActiveSheet.PivotTables("ピボットテーブル1")
　With .PivotFields("順番")
　　　.Orientation = xlColumnField
　　　.Position = 1
　End With
　With .PivotFields("区分")
　　　.Orientation = xlColumnField
　　　.Position = 2
　End With
　With .PivotFields("区分名")
　　　.Orientation = xlColumnField
　　　.Position = 3
　End With
　　　:
　　　:
　　　:
End With
　
　
また次のようにするともっとすっきりするかも知れません。
　
With ActiveSheet.PivotTables("ピボットテーブル1")
　For I = 1 To .PivotFields.Count
　　　With .PivotFields(I)
　　　　　.Orientation = xlColumnField
　　　　　Select Case .Name
　　　　　　　Case "順番": .Position = 1
　　　　　　　Case "区分": .Position = 2
　　　　　　　Case "区分名": .Position = 3
　　　　　　　：
　　　　　　　：
　　　　　　　：
　　　　　End Select
　　　End With
　Next
End With

この回答への補足

ありがとうございます。
Select Caseですが

順番、区分、区分名は　xlColumnField
品番、仕入、品名、棚番は　xlRowField

ですので

With ActiveSheet.PivotTables("ピボットテーブル1")
For I = 1 To .PivotFields.Count
With .PivotFields(I)
.Orientation = xlColumnField
Select Case .Name
Case "順番": .Position = 1
Case "区分": .Position = 2
Case "区分名": .Position = 3
End Select

.Orientation = xlRowField
Select Case .Name
Case "品番": .Position = 1
Case "品名": .Position = 2
Case "仕入": .Position = 3
Case "棚番": .Position = 3
End Select
End With
Next
End With

かなと思ったのですが、
Case "順番": .Position = 1
Case "区分": .Position = 2
Case "区分名": .Position = 3
の部分で
「順番」しかセットされません。

Case "品番": .Position = 1
Case "品名": .Position = 2
Case "仕入": .Position = 3
Case "棚番": .Position = 3
の部分で
「品番」しかセットされませんでした。
ちょっと自力で組み立てができそうも
ないです。せっかく教えていただいたのですが
能力不足で申し訳ないです。

補足日時：2011/03/14 16:40

この回答へのお礼

ありがとうございます。

手操作ですと
ピボットテーブルのレポートで
ピボットテーブルのツールオプションで

【列範囲】を選択して
・順番→追加
・区分→追加
・区分名→追加
と操作します。
セルだと
・順番（B3）
・区分（C3）
・区分名（D3）
にセットされます。

【行範囲】にて
・品番→追加
・仕入→追加
・品名→追加
・棚番→追加と操作します。
セルだと
・品番（A6）
・仕入（B6）
・品名（C6）
・棚番（D6）
にセットされます。
結果最初にセットされた列は
・順番（B3）→（E3）
・区分（C3）→（F3）
・区分名（D3）→（G3）
になります。

回答A-NO.1さんのお礼に記載しましたが、

With ActiveSheet.PivotTables("ピボットテーブル1")
.PivotFields("順番").Orientation = xlColumnField
.PivotFields("区分").Orientation = xlColumnField
.PivotFields("区分名").Orientation = xlColumnField
.PivotFields("品番").Orientation = xlRowField
.PivotFields("仕入").Orientation = xlRowField
.PivotFields("品名").Orientation = xlRowField
.PivotFields("棚番").Orientation = xlRowField
.AddDataField ActiveSheet.PivotTables("ピボットテーブル1")_
.PivotFields("数量"), "合計 / 数量", xlSum
End With

と.Position = ●は省いた構文で
上記説明の手操作のとうりにセットされたのですが
教えていただいた構文は.Position = ●が入っています。
やはり.Position = ●は指定した方が安全なのでしょうか？
重ね重ねすいません。

お礼日時：2011/03/14 16:58

No.1 ベストアンサー 回答者： nmktksk 回答日時： 2011/03/14 14:17

ActiveSheet.PivotTables("ピボットテーブル1")

ここまでをwithで宣言してその先は普通に書いたほうがいいと思います。

この回答への補足

最初の方はもう1行構文がありました。

ActiveSheet.PivotTables("ピボットテーブル1").AddDataField_ ActiveSheet.PivotTables( "ピボットテーブル1")_
.PivotFields("数量"), "合計 / 数量", xlSum

です。これも先頭の

ActiveSheet.PivotTables("ピボットテーブル1

を削除して最後尾につけました。

With ActiveSheet.PivotTables("ピボットテーブル1")
.PivotFields("順番").Orientation = xlColumnField
.PivotFields("区分").Orientation = xlColumnField
.PivotFields("区分名").Orientation = xlColumnField
.PivotFields("品番").Orientation = xlRowField
.PivotFields("仕入").Orientation = xlRowField
.PivotFields("品名").Orientation = xlRowField
.PivotFields("棚番").Orientation = xlRowField

.AddDataField ActiveSheet.PivotTables("ピボットテーブル1")_
.PivotFields("数量"), "合計 / 数量", xlSum

End With

補足日時：2011/03/14 15:45

この回答へのお礼

少し考え過ぎていたみたいです。
以下のようにしました。

With ActiveSheet.PivotTables("ピボットテーブル1")
.PivotFields("順番")
.Orientation = xlColumnField
.Position = 1

エラーになりました。デバックやヘルプで調査して
原因が分かりました。

With ActiveSheet.PivotTables("ピボットテーブル1")
.PivotFields("順番").Orientation = xlColumnField
.Position = 1

なのですね。また.Position = 1は不要みたいなので
（少々不安。でも思っていた位置にセットされたので
　いいと思います．．．．．．）
結果は以下の構文でマクロの記録と同じ処理になりました。

With ActiveSheet.PivotTables("ピボットテーブル1")
.PivotFields("順番").Orientation = xlColumnField
.PivotFields("区分").Orientation = xlColumnField
.PivotFields("区分名").Orientation = xlColumnField
.PivotFields("品番").Orientation = xlRowField
.PivotFields("仕入").Orientation = xlRowField
.PivotFields("品名").Orientation = xlRowField
.PivotFields("棚番").Orientation = xlRowField
End With

また後半は以下↓の構文で大丈夫でした。
どうもありがとうございました。

With ActiveSheet.PivotTables("ピボットテーブル1")
.PivotFields("品番").PivotItems("(空白)").Visible = False
.PivotFields("仕入").PivotItems("(空白)").Visible = False
.PivotFields("品名").PivotItems("(空白)").Visible = False
.PivotFields("棚番").PivotItems("(空白)").Visible = False
.PivotFields("順番").PivotItems("(空白)").Visible = False
.PivotFields("区分").PivotItems("(空白)").Visible = False
.PivotFields("区分名").PivotItems("(空白)").Visible = False
End With

お礼日時：2011/03/14 15:31