VBAでの作業グループ全体の操作

質問

こんにちは、２度目の投稿です。
エクセル複数シートを作業グループ化してセルに値を入力したり色をつけたりすると、作業グループ全体に反映する機能があると思うのですが、これをVBAで記述したいと思っています。
調べると、作業グループで選択したシートはActiveWindow.SelectedSheetsに格納されるとあったので、例えば行を追加する場合、
For Each s In ActiveWindow.SelectedSheets
s.Selection.EntireRow.Insert
Next s
で実行されるかと思いきや・・・同じシートに作業グループのシート数だけ行が増えたのです(・・;)
セルの色を付けたい場合も、
For Each s In ActiveWindow.SelectedSheets
　　s.Range("A2").Interior.Color = RGB(255, 255, 0)
　　Next s
これだと１シートにしか色がつかなかったり、インディックス範囲のエラーです・・・┐('～`；)┌
SelectedSheetsというのはコレクションで、Rangeはオブジェクトだから、、、えっと、、、と混乱してきてしまいました。
どのように記述すればいいのでしょうか？
よろしくお願いします。

回答No.4

taocat · ベストアンサー

こんばんは。

ではちょっとだけ説明しますね。

（処理内容）
Sheet1,Sheet2,Sheet3　が選択されていて、
Sheet1がActiveSheetで、
セルA1:B2　に色をつける場合。

Sub Test()
　Dim s As Worksheet
　For Each s In ActiveWindow.SelectedSheets

　　●s.Range("A1:B2").Interior.ColorIndex = 6

　　▲s.Range(Cells(1, 1), Cells(2, 2)).Interior.ColorIndex = 6

　Next s
End Sub

上記コードで●はＯＫなのに▲では何故エラーがでるかというとCellsの前の親オブジェクト（Sheet）が省かれているからです。
Rangeオブジェクトの親オブジェクト(Sheet）を省くと暗にActiveSheetを指定していることになるので、Sheet1を処理している時はいいのですが、次のSheet2を処理するときにエラーがでます。
つまり、Sheet2を処理しようとしているにも拘わらず、Cellsで指定しているセルはActiveSheetであるSheet1のセルを指定していることになり矛盾が起こっているというわけです。

このことは、s.Selectと入れることにより、処理しようとするシートをActiveにすれば上手くいくことからも分かります。。。よね？

で、以下のようにCellsの前に、Sheetオブジェクトを付加すれがＯＫです。またこれだとActiveSheetももとのままです。

Sub Test()
　Dim s As Worksheet
　For Each s In ActiveWindow.SelectedSheets

◎　　s.Range(s.Cells(1, 1), s.Cells(2, 2)).Interior.ColorIndex = 6

　Next s
End Sub

---------------------------------------------------------------------------

それから、行挿入も次のようにすればActiveSheetは変わりません。

Sub Test()
　Dim s As Worksheet
　For Each s In ActiveWindow.SelectedSheets
　　　s.Range(Selection.Address).EntireRow.Insert xlShiftDown
　Next s
End Sub

---------------------------------------------------------------------------

何れにしろ複数のシートを扱うときは処理したいセルがどのシートのセルなのか常に考えておかなければいけないわけですね。

ちょっと分かり難かったかも知れませぬねぃ。(^^;;;
以上です。

お礼

「上記コードで●はＯＫなのに▲では何故エラーがでるかというと」
という点について、疑問が残っていたのですがこれで理解できました。
本当にご丁寧にありがとうございました。

回答No.3

taocat

こんにちは。

＞＞だって、作業グループって選択したシートに同じ操作を加えるために設定するんですよね？それを個別に操作しようするからセレクトしていったん作業グループを解除しないと無理なんではないでしょうか？

＞なるほど・・・。
＞大変わかりやすい説明をどうもありがとうございました！

こら、こら、(^o^;;;;

お二人ともシートとセルの関係がこんがらがっておりませぬか。
ちょとコードをアップしようかとも思ったのですが、
既に解決みたいな雰囲気ですのでもうここは見ないでしょうから
次回似たようなものがあった時にでも。(^^;

以上です。

補足

実はまだ疑問は残っていますので、回答を締め切っていません・・・。
もう少し教えてもらえませんでしょうか？

回答No.2

merlionXX

> 行と同じように、s.selectを前に入れればできたのですが、そうするとアクティブシートが移動してしまったので、

だって、作業グループって選択したシートに同じ操作を加えるために設定するんですよね？それを個別に操作しようするからセレクトしていったん作業グループを解除しないと無理なんではないでしょうか？

お礼

なるほど・・・。
大変わかりやすい説明をどうもありがとうございました！

回答No.1

merlionXX

こんにちはnao_linさん、先日はどうも。

行を追加する場合、

For Each s In ActiveWindow.SelectedSheets
s.Select
Selection.EntireRow.Insert
Next s

と、いったんシートを選択してください。

For Each s In ActiveWindow.SelectedSheets
　　s.Range("A2").Interior.Color = RGB(255, 255, 0)
Next s
これは大丈夫だと思いますが・・・。

補足

merlionXXさん、お世話になります！
行を追加する場合、OKでした。ありがとうございました！
しかし、セルの色のところですが、実は、rangeのところはセルで指定していました、
Range(cells(1,1),cells(2,2)).Interior.Color
というようにしているのです。
ですので、
s.Range(以下同じ）
とすると
アプリケーション定義、オブジェクト定義のエラー
となってしまうのです。
なぜでしょうか？
行と同じように、s.selectを前に入れればできたのですが、そうするとアクティブシートが移動してしまったので、作業前に最初のシート名を取得しておいて、最後にまた最初のシートをアクティブにしたのですが、、、とりあえずは解決なんですけど、疑問が残ります。教えてください。