エクセルVBAでシートコピー

質問

Sheets("Sheet1")をCopyして、現在の最終のシートの後に持っていき、それを変数wsに設定しようとしています。
Sub TEST01()
Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
Set ws = Sheets(Sheets.Count)
MsgBox ws.Name
End Sub
これで通常はうまくいきますが、最終シートが非表示になっているとwsはその非表示のシートが指定され、Sheets("Sheet1")をCopyしたものでなくなってしまいます。

Sheets("Sheet1")をCopyして、現在の最初のシートの前に持っていき、それを変数に設定しようとしています。
Sub TEST02()
Sheets("Sheet1").Copy Before:=Sheets(1)
Set ws = Sheets(1)
MsgBox ws.Name
End Sub
これで通常はうまくいきますが、最初のシートが非表示になっているとwsはその非表示のシートが指定され、Sheets("Sheet1")をCopyしたものでなくなってしまいます。

Set ws = ActiveSheet で設定できましたが、それ以外の方法はないでしょうか？

回答No.1

real beatin（@realbeatin） · ベストアンサー

こんにちは。

worksheets.Add メソッド を使った例でお応えします。

個人的な話として私の場合は、Excelを始めて数ヶ月後ぐらいから、
シートそのものをコピーすることはしないようになっていったので、
寧ろ私には不得手なジャンルではあります。
思い出すまでに（昔書いたものを探すのに）時間が掛かってしまいましたが、
以前はこんな風にして、
コピー後のシートを非表示シートの外側に配置していました。
もっとスッキリしたやり方、あるのかもしれませんけれど。

＃えーと、解答コード３例に共通して、
　.Addメソッドは戻り値として、Worksheet等のオブジェクトを返すこと、
　.Addメソッドなら、非表示sheetの外側に簡単にシートを挿入できること、
今回の課題については、この２点だけ解れば、内容的にはどれも易しいです。

' ' /// Sheets("Sheet1")をCopyして、現在の最終のシートの後に
Sub ReW9070896aZ()
'　　With Sheets
　　With Worksheets
　　　　Set ws = .Add(After:=.Item(.Count))　'　▼。
　　　　.Item("Sheet1").Copy After:=ws　 '　▼
　　End With
　　Set ws = ws.Next
　　Application.DisplayAlerts = False
　　ws.Previous.Delete
　　Application.DisplayAlerts = True

　　MsgBox ws.Name
End Sub
' ' ///
' ' /// 現在の最初のシートの前に
Sub ReW9070896aA()
'　　With Sheets
　　With Worksheets
　　　　Set ws = .Add(Before:=.Item(1))　　　'　▲
　　　　.Item("Sheet1").Copy Before:=ws　'　▲
　　End With
　　Set ws = ws.Previous
　　Application.DisplayAlerts = False
　　ws.Next.Delete
　　Application.DisplayAlerts = True

　　MsgBox ws.Name
End Sub
' ' ///

シートそのものをＶＢＡでコピーする、ということには個人的に消極的です。
シートを追加してセル範囲（だけ）をコピーすることを優先しています。
ただ、この方法（このまま）だと、コピーされないものがあります。
まず、シートモジュール。
Application.CopyObjectsWithCells = False を設定してから実行する場合は、
各種オブジェクト（コメント.ドロップダウン以外のシェープ全般）。
私の考えでは、これらはコピーされない方が何かとトラブルフリーなので、
セル範囲以外がコピーされないことで困るようなヘビーな
シート（ブック、システム）設計は、採らないようにしています。
設計に拘らなかったとしても、場合によっては、却って扱い易い手法ですから、
普通のシートコピーとの違いを知った上で、使い分けて貰えたらなぁと思います。

　　Application.CopyObjectsWithCells = False
を設定してから実行した方が論旨一貫しますが、この点は必要に合わせてください。

' ' /// シートを追加してセル範囲をコピーする
Sub ReW9070896c()
'　　With Sheets
　　With Worksheets
　　　　Set ws = .Add(After:=.Item(.Count))　'　▼
'　　　　Set ws = .Add(Before:=.Item(1))　　　'　▲
　　　　.Item("Sheet1").Cells.Copy Destination:=ws.Cells(1)
　　End With

　　MsgBox ws.Name
End Sub
' ' ///

お礼

realbeatinさん、いつも助けていただきありがとうございます。
なるほど、いったんダミーのシートを最終シートとしてAddしてしまえば非表示シートは気にする必要がないのですね！
また、ws.Previousやws.Nextという使い方は初めて知りました。
Application.CopyObjectsWithCells = Falseも勉強になりました。
ありがとうございました。

回答No.2

watabe007

コピーされたシートはアクティブになるのを利用すれば
Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
Set ws = ActiveSheet
MsgBox ws.Name

お礼

ありがとうございます。
ただ、コピーされたシートがアクティブになることは存じており、それで質問には
Set ws = ActiveSheet で設定できましたが、それ以外の方法はないでしょうか？
と書いたのですが・・・・