Home » エクセルマクロ・Excel VBAの使い方 » Sheets・Worksheet » マクロでシートコピー時にアクティブシートを変更しないように

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

対象:Excel2007, Excel2010, Excel2013

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

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

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

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

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

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

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

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

[スポンサードリンク]

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

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

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

Sub アクティブシートを切り替えずに最後尾にコピーする()
 Dim sh As Object

 Set sh = ActiveSheet
 ActiveSheet.Copy After:=Sheets(Sheets.Count)
 sh.Select
End Sub

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

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

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

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

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

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

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

Sub アクティブシートを切り替えずに最後尾にコピーする_普通の変数()
 Dim n As Long

 n = ActiveSheet.Index
 ActiveSheet.Copy After:=Sheets(Sheets.Count)
 Sheets(n).Select
End Sub

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

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

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

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

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

Sub アクティブシートを切り替えずに先頭にコピーする()
 Dim n As Long

 n = ActiveSheet.Index
 ActiveSheet.Copy Before:=Sheets(1)
 Sheets(n + 1).Select
End Sub

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

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

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

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

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

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

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

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

[スポンサードリンク]

Home » エクセルマクロ・Excel VBAの使い方 » Sheets・Worksheet » マクロでシートコピー時にアクティブシートを変更しないように

「Sheets・Worksheet」の記事一覧

検索


Copyright © インストラクターのネタ帳 All Rights Reserved.

.