和風スパゲティのレシピ

日本語でコーディングするExcelVBA

トップ > ワークシート > 非表示のシートをコピー・移動する

非表示のシートをコピー・移動する

ワークシート サンプルコード VBA

非表示のシートをコピー・移動しようとすると、

実行時エラー '1004':
このシートをコピーできませんでした。

エラーが発生します。


これを非表示のまま解消することはできませんので、
「一旦表示 → コピー → 非表示に戻す」という手順を踏んで対応します。

基本コード

Dim wsコピー元 As Worksheet
Set wsコピー元 = Worksheets("○○")

' 一旦シートを再表示
wsコピー元.Visible = True

' シートをコピー（＋作られたシートを変数に格納）
wsコピー元.Copy
Dim ws生成シート As Worksheet
Set ws生成シート = ActiveSheet

' コピー元シートを再度非表示に
wsコピー元.Visible = False

' 生成シートへの処理など
ws生成シート.Range("A1").Value = 1

解説

前述の通り「再表示 → コピー → 非表示」という手順を行っているコードです。

シートの表示/非表示はVisibleプロパティをTrue/Falseにするだけなので、
コード全体もシンプルに書けますね。


シートの移動を行いたい場合は、Copy部分をMoveに変えるだけでOKです。


同じシートへの処理が続きますので、しっかりシート変数にSetして使いましょう。

変数以外の方法として、Withステートメントで行うのも便利です。

With Worksheets("○○")
    .Visible = True
    
    .Copy
    Dim ws生成シート As Worksheet
    Set ws生成シート = ActiveSheet

    .Visible = False
End With

　
ついでになりますが、生成されたシートもしっかり変数に入れた方が良いです。

Copyメソッドは返り値にWorksheetを返してくれないため、
そのシートをキャッチするにはActiveSheetを取得しなければいけません。


そのままActiveSheetへの処理を書いていると、
思わぬところでActiveSheetが変わって不具合を呼びます。

即座に変数にSetして以降の処理を行いましょう。

汎用関数化しておく

テンプレートシートをコピーして使うなど、
この処理をよく行う場合は汎用関数にしておくのをおすすめします。

汎用関数を利用したコードのサンプルがこちらです。

' 汎用関数使用例
Sub サンプルマクロ()
    
    ' 非表示のテンプレートシートを新規ブックにコピー
    Dim ws出力シート As Worksheet
    Set ws出力シート = 非表示シートを新規ブックにコピーする(Worksheets("○○"))

    ' 以降出力シートへの処理
    ws出力シート.Range("A1").Value = 1

End Sub

' 非表示シートをコピーする汎用関数
Function 非表示シートを新規ブックにコピーする(wsコピー元 As Worksheet) As Worksheet
    
    wsコピー元.Visible = True
    wsコピー元.Copy
    Set 非表示シートを新規ブックにコピーする = ActiveSheet
    wsコピー元.Visible = False
    
End Function

シートのコピーがメインコード上で1行になって、
かなり読みやすく&書きやすくなっているのが分かりますね。

Visible→Copy→Visibleが1行になっているのもそうですが、
FunctionにしてActiveSheetを返しているのも便利なポイントです。


この「CopyメソッドがWorksheetを返してくれない問題」は、
普通の表示シートでも起こる問題です。

そこで今回の汎用関数をさらに発展させ、
シートの表示/非表示に関わらず動くようにしたコードがこちらです。

' 汎用関数使用例
Sub サンプルマクロ()
    
    ' テンプレートシートを新規ブックにコピー
    Dim ws出力シート As Worksheet
    Set ws出力シート = シートを新規ブックにコピーする(Worksheets("○○"))

    ' 以降出力シートへの処理
    ws出力シート.Range("A1").Value = 1

End Sub

' シートを新規ブックにコピー
Function シートを新規ブックにコピーする(wsコピー元 As Worksheet) As Worksheet
        
    Dim isコピー元が非表示 As Boolean
    isコピー元が非表示 = (wsコピー元.Visible = False)
    
    If isコピー元が非表示 Then wsコピー元.Visible = True
    
    wsコピー元.Copy
    Set シートを新規ブックにコピーする = ActiveSheet
    
    If isコピー元が非表示 Then wsコピー元.Visible = False

End Function

コピー元シートが非表示だったかどうかをBoolean変数に記憶しておき、
非表示だった場合はコピー後に非表示に戻しているコードです。

これでシートの表示/非表示に関わらずコピー処理が行えますし、
非表示だったら戻すという処理も裏で行ってくれるから便利ですね。


このように、「簡単だけど書くのが面倒なコード」は、
関数化が簡単な割に、汎用関数にするメリットが大きいです。

汎用関数集を作っている方は、ぜひそのメンバーに加えてあげてください。


汎用関数集の作り方・使い方についてはこちらをどうぞ。

www.limecode.jp