エクセルVBAでシート一括作成したらはまった話

2021.10.18

表題の件ではまってしまったので備忘録。

結論から書く。

何も考えずに以下のコードでシートをコピーすると、処理後コピーしたシートに自動で移動してしまうという仕様を知らなかったことが原因。

Worksheets("sheet2").Copy

色々と振り回されてしまった。難しいねえ。

機能要件

１．VBAを利用してsheet2をコピーしたい

２．コピーしたファイルを末尾に配置したい

３．コピー時にsheet1のA列、B列を組み合わせた名前で作成したい（例：シート名　A1_B1）

４．sheet1に実行ボタンを配置し、そのボタンを押すことで一括作成したい

５．A1が5行ある場合は５個、10行ある場合は10個シートを作成したい

実装

１．シートのコピー

シートのコピーは以下のコードで実行。実行するとsheet21というシートが新規作成される。

Sub sheet1_ボタン1_Click()

Worksheets("sheet2").Copy After:=Worksheets(Worksheets.Count)
name = "sheet21"
ActiveSheet.Name = name


End Sub

ファイルが作成されたことを確認。
これで機能要件１、２は対応完了。

２．sheet1のA列、B列の値の取得

A列とB列の値を取得。今回はテストとして結合して作成した値「A列_ｂ列」をC列に書き込む。

Sub sheet1_ボタン1_Click()

      Dim i As Long: i = 3
      Do
      If Cells(i, 1).Value = "" Then
          Exit Do
      End If
          Cells(i, 3) = Cells(i, 1) & "_" & Cells(i, 2)
      i = i + 1
      Loop

End Sub

実行後、C列に5行書き込まれたことを確認。

これで機能要件３，４，５は対応完了。
あとは、「 Cells(i, 3) ～」の行を１に１のコードを組み合わせたら問題ないと思っていた。

３．１と２を組み合わせたらループが処理されなかった。

「 Cells(i, 3) ～」 のところだけ修正し、以下のコードを実行したが最初の行しか処理されず。

Sub sheet1_ボタン1_Click()
 
      Dim i As Long: i = 3
      Do
      If Cells(i, 1).Value = "" Then
          Exit Do
      End If
      
      Dim sht_cp As Variant
      sht_cp = Cells(i, 1) & "_" & Cells(i, 2)
      Worksheets("Sheet2").Copy After:=Worksheets(Worksheets.Count)
      ActiveSheet.Name = sht_cp
      i = i + 1
      Loop

End Sub

結果は以下の通りA3列、B3列のデータしか処理されず。
意味が分からないし原因がわからない。原因特定のため調査開始。

原因特定　VBAでシート作成するときの残念仕様？の影響と判断

色々調べたところ、 Worksheets(“****”).Copyを利用すると自動でアクティブシートが新規作成したシートに移動してしまうらしいことを発見。

不細工なやり方だが、loop処理の最後に元のシート、sheet1に戻る処理<Sheets(“Sheet1”).Select>を追加することでループ処理を行うことができるようにした。

すぐに忘れると思うので今後のために記載。

Sub sheet1_ボタン1_Click()
 
      Dim i As Long: i = 3
      Do
      If Cells(i, 1).Value = "" Then
          Exit Do
      End If
      Dim sht_cp As Variant
      sht_cp = Cells(i, 1) & "_" & Cells(i, 2)
          Worksheets("Sheet2").Copy After:=Worksheets(Worksheets.Count)
          ActiveSheet.Name = sht_cp
          Sheets("Sheet1").Select
      i = i + 1
      Loop

End Sub

上記のコードで実行することで対応完了。めでたしめでたし。

まとめ

VBAって難しい。自分が勉強不足なだけやけれど。気を付けていきまっしょい。

あと、同一名称のシートを作成した時のエラー処理や、マスターシート(コピー元)のシートの変更内容（レイアウト、数式等）を作成済みのシートに反映する処理を用意しないといけない。

なのでそこも忘れないようにしなければ。

コメント

タイトルとURLをコピーしました