マクロでシートコピー時にアクティブシートを変更しないように

「excelvba シート コピー 画面を切り替えたくない」
という検索で、このサイト・インストラクターのネタ帳へのアクセスがありました。

VBA（Visual Basic for Applications）で、シートのコピーを行うと、コピーして作られた新しいシートがアクティブになります。

これを、アクティブなシートを切り替えないようにする方法を探している方による検索でしょうか。

WorksheetオブジェクトのCopyメソッドも、

ChartオブジェクトいずれのCopyメソッドも、

指定できる引数は、どこにコピーするかというBeforeとAfterだけです。

コピー時には新しいシートがアクティブになるのがExcelの仕様ですから、アクティブなシートを切り替えずにシートのコピーを行うには、アクティブなシートを覚えておいて、コピー後に元々アクティブだったシートを選択し直すという処理が必要になります。

アクティブシートを切り替えず最後尾にコピーするサンプルマクロ

以下のようなマクロで、アクティブなシートを変更しないで、シートをコピーすることができます。

オブジェクト変数・shにアクティブなシートを格納して、
　Set sh = ActiveSheet

アクティブシートを最後尾にコピーし、
　ActiveSheet.Copy After:=Sheets(Sheets.Count)

先にオブジェクト変数に格納したシートを選択しています。
　sh.Select

VBE（Visual Basic Editor）のショートカットキー［F8］を使ってステップ実行すると、処理の様子がよくわかるでしょう。

画面のチラつき（更新）もなくしたいのなら、コピーの前に画面の更新を停止するコード、
　Application.ScreenUpdating = False
を入れてください。

オブジェクト変数を使わずアクティブシートを切り替えないでシートをコピーする

上記の例のようにシートそのものをオブジェクト変数に格納するのではなく、何枚目のシートかという値を普通の変数に格納しておくという方法でも、もちろん実装できます。

アクティブなシートが何枚目なのかをLong型の変数・nに格納して、
　n = ActiveSheet.Index

シートコピーを行ったあとで、変数に格納された値を使ってシートを選択しています。
　Sheets(n).Select

オブジェクト変数を使わずアクティブシートを切り替えずシートコピーするときの注意点

何枚目のシートかを、普通の変数に格納しておくという方法の場合、シートをどこにコピーするかによってコードが異なるので注意が必要です。

例えば、最後尾ではなく先頭にコピーするというマクロの場合は、以下のようなコードにする必要があります。

ポイントは、シートを先頭にコピーしたあとに、
　ActiveSheet.Copy Before:=Sheets(1)

元々アクティブだったシートを選択する、以下の部分です。
　Sheets(n + 1).Select

Sheetsプロパティの引数が、最後尾にコピーするマクロの場合は「n」だったのに、このマクロでは「n + 1」になっています。

シートが先頭にコピーされた関係で、元々アクティブだったシートのIndexは、一つ大きくなるためSheetsプロパティの引数を「n + 1」にして「Sheets(n + 1)」とする必要があります。

この例のように先頭にコピーする場合だけでなく、アクティブなシートより前（左側）にコピーするときは、元々のアクティブシートのIndexは必ず大きくなりますから「+ 1」が必要になります。

オブジェクト変数と普通の変数

最初にご紹介したオブジェクト変数を使ったマクロでは、どこにコピーしたのかを気にする必要はありませんが、普通の変数を使う場合はどこにコピーするかによってコードが異なるというのがポイントです。

今回のSheetsのように、コレクションオブジェクトを返すプロパティを使う場合、普通の変数でもオブジェクト変数でもコードを書くことができますが、今回のケースはオブジェクト変数のほうが有利と言える例です。