【Excel/VBA】テーブルデータをもとに複数のファイル作成②～クラス　シートモジュール　ディクショナリの実験的試行錯誤

前回、クラス、シートモジュールを使ってテーブルデータをもとにした複数ファイルの作成をやってみました。

【Excel/VBA】テーブルデータをもとに複数のファイル作成①～クラス　シートモジュール　ディクショナリの実験的試行錯誤

今回はそれのちょっと変化版。テーブルデータをもとに書式シートをコピーしてデータを入力しファイルを作成するのは同じだが、ファイルを地域ごとにして、地域ファイルに複数店舗のシートが含まれるようにする。

（１）テーブルデータ

「TB」シートにデータを入力しておく

no , tenpo , area
1 , 北海道店 , 北海道
2 , 福島店 , 東北
3 , 青森店 , 東北
4 , 東京店 , 関東
5 , 埼玉店 , 関東
6 , 福岡店 , 九州
7 , 長崎店 , 九州

仕上がりは

「北海道」ファイル：北海道店のシート
「東北」ファイル：青森店、福島店のシート
「関東」ファイル：東京店、埼玉店のシート
「九州」ファイル：福岡店、長崎店のシート

（２）コード：シートモジュールに書く

1 Sub 複数シートのファイル複製()

2 Application.ScreenUpdating = False
3 Application.DisplayAlerts = False

4 Dim path As String ‘ファイルを保存するフォルダ
5 path = ThisWorkbook.path & “\”

6 Dim wsGen As Worksheet ‘原本シートセット
7 Set wsGen = Worksheets(“原本”)

8 Me.getdata ‘データセット

9 Dimi As Long, wsGen2 As Worksheet, vArea As String, key As String

10 i = 2

11 key = Me.Cells(i, 1).Value

12 Do Until key = “”
13 wsGen.Copy
14 Set wsGen2 = ActiveSheet
15 Do
16 wsGen2.Copy Before:=wsGen2
17 Me.inputdata key
18 vArea = myDic(key).area
19 i = i + 1

20 key = Me.Cells(i, 1).Value
21 If key = “” Then
22 Exit Do
23 End If

24 Loop Until myDic(key).area <> vArea

25 wsGen2.Delete
26 With ActiveWorkbook
27 .SaveAs Filename:=path & vArea & “.xlsx”
28 .Close
29 End With
30 Loop

31 Set myDic = Nothing

32 Application.ScreenUpdating = True
33 Application.DisplayAlerts = True

34 End Sub

1～8までは（テーブルシートのデータをディクショナリにストックするところまで）前回と同じ

10：変数 i に２を代入（Tbシートの2行目から読み取るため）

11：変数keyにTBシートA列の2行目のセルの値を代入。

自分自身のシートとして明示するためMe.Cells(i,1)とした。Meは記述せずCellsとしても同じ。

12～30:Loop中にiに1をプラスし、行番号30までの処理を繰り返し。変数keyの値が空白になったらLoop抜ける。

13：原本シートコピー

14：コピーしたシートをwsGen2にセットしておく

15～24：15～24繰り返し処理。

16：wsGen2シートコピー

17：inputdata呼び出し。keyを引数として渡す。シートに当該keyのデータを入力。

18：当該keyの地域を変数ｖAreaに入れておく

19：変数 i に１プラス

20：変数keyにTBシートA列の該当行のセルの値を代入。ここでも自分自身のシートを参照するためMe.Cellsとする。

21～23：（１つめのLOOP抜け判定）変数 key が空白の場合Loopをぬけて25行目に行く

24：（２つめのLOOP抜け判定）area（地域）が前の地域と違う場合Loopを抜ける。

25～29：ファイル保存の処理。行番号25でwsGen2削除。行番号27で地域をファイル名として名前を付けて保存。行番号28でファイルを閉じる

31：ディクショナリの破棄

行番号25でシートを削除するときのアラートが出ないように行番号3でアラートでないようにして、行番号33でアラートでない処理を解除

２と32は画面更新ストップ・解除

行番号8と18は、自分自身’（シートモジュール）のプロシージャを呼び出すので、Me.getdata、Me.inputdata keyとしたが、Call inputdata(key)でも同じ。

ポイントはテーブルが地域順であること。地域順になっていることで地域の切り替わりでファイルの切り替えができる。

For Eachでディクショナリのキーを回した場合、データを登録した順番に出てくるのかどうかわからなかったのと、地域が変わったときの切り替えの書き方が分からなかったので、For Eachはやめた。

コレクションならインデックス順でまわせるのでコレクションにしようか・・・とも思ったけれど、地域順になっているTBシートのkey（店舗no)を読み込んでいけばいいよね、という結論になり上記のコードにしました。

※コード中で呼び出しているinputodataとgetdataのコードは前回記事にあります。