この記事では、ひな型のEXCELシートをコピーして、大量に新規ブックを作成する方法について説明しています。
定形のひな形シートを利用して、新規ブックを大量に作成したい場合に使えます。
また、ひな形の一部は配布する相手によって、変えることができます。
例えば、アンケート内容をひな形シートで作成しておき、配布する対象者毎に名前や従業員番号などを変更して、新規ブックを作成することができます。
しかも、定期的に配布する必要があるアンケートや勤怠表などをひな形シートとして作成しておけば、コピーしたいひな形を切り替え可能となっています。
一度この機能を作成しておけば、たくさんの従業員や顧客に配布する資料を簡単に大量に一気に作成することできるようになります。
あなたが用意するのは、配布先リストとひな形シートだけです。
あとは、VBAプログラムを実行すれば、一気に資料作成を終わらせることができます。
それでは、ひな形のEXCELシートをコピーして、大量に新規ブックを作成する機能を作成していきましょう。
困っている女性
困っている女性
困っている女性
解決する男性
解決する男性
困っている女性
ひな型のEXCELシートをコピーして、大量に新規ブックを作成する機能に必要なものを、1つ1つ順番に説明していきます。
もくじ
１．配布先リストを記入するシートを作成する
まず、新規ブックを作成する「作成先フォルパス」、コピーしたい「ひな形シート名」、作成したい新規ブックに必要な情報の「配布先リスト」を入力する欄を作成します。
「メイン」シートを作成する
「メイン」シートを作成する
- 「メイン」という名前のシートを作成します。
- 1行目に「作成先フォルダパス」と「ひな形シート名」のタイトル行を作成します。
- 2行目に「作成先フォルダパス」と「ひな形シート名」を入力する欄を作成します。
- 3行目に「作成ファイル名」と「シート名」のタイトル行を作成します。
- 4行目以降は、「作成ファイル名」と変更したい「シート名」を入力する欄を作成します。
- C列からL列の1行目と2行目のセルを結合して、「差込位置」のタイトル行を作成します。
- C列からL列の3行目にひな形シートの差込したいセル位置を入力する欄を作成します。
- C列からL列の4行目以降は、差込したいセル位置に入力したい値を入力する欄を作成します。
ちなみに、ひな形に差込したい情報は最大10個まで指定が可能です。
差込したい情報がない場合、入力する必要はありません。
２．ひな形シートを作成する
次に、コピーしたいひな形シートを好きなシート名で作成します。
「ひな形」シートを作成する
ひな形シートを作成する
ひな形シートは、好きなレイアウトで作成します。
サンプルとして、「アンケートA」というシート名でアンケートを作成しました。
- B3セルには、「部署名」を差込できる欄を用意しました。
- C3セルには、「従業員番号」を差込できる欄を用意しました。
- D3セルには、「氏名」を差込できる欄を用意しました。
また、ひな形シートは好きなだけ準備しておいて問題ありません。
利用したいひな形シート名を「メイン」シートで指定すれば、そのひな形シートをコピーして新規ブックが作成されます。
顧客に配布する資料の場合、会社名や担当者名を差込できるようにひな形シートを作成すると良いです。
それでは、好きなひな形シートを作成しましょう。
３．VBAでCSVファイルを作成する機能を記入する
「Microsoft Visual Basic for Applications」を起動します。
VBAプログラムを記入する（その1）
VBAプログラムを記入する（その2）
標準モジュールを追加して、以下のソースを書きます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
Public Sub MainProc() Dim shtMain As Worksheet Dim shtHina As Worksheet Dim folder As String Dim hinaName As String Dim fso As Object Dim maxRow As Long Dim maxCol As Long Dim varIchi As Variant Dim varData As Variant Dim i As Long Dim j As Long '１．「メイン」シートを変数に格納する Set shtMain = ThisWorkbook.Sheets("メイン") '２．作成先フォルダパスを変数に格納する folder = shtMain.Cells(2, 1) '３．ひな形シート名を変数に格納する hinaName = shtMain.Cells(2, 2) '４．データ入力されているA列の最終行を取得する maxRow = shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row '５．データ入力されている3行目の最終列を取得する maxCol = shtMain.Cells(3, shtMain.Columns.Count).End(xlToLeft).Column '６．差込位置情報を取得する If maxCol > 2 Then varIchi = shtMain.Range(shtMain.Cells(3, 3), shtMain.Cells(3, maxCol)) End If '７．作成ファイル情報を取得する varData = shtMain.Range(shtMain.Cells(4, 1), shtMain.Cells(maxRow, maxCol)) '８．ひな形シートを変数に格納する Set shtHina = ThisWorkbook.Sheets(hinaName) '９．指定されたひな形シートを新しいブックへコピーする shtHina.Copy For i = 1 To UBound(varData) '１０．シート名を変更する ActiveWorkbook.Sheets(1).Name = varData(i, 2) If maxCol > 2 Then For j = 1 To UBound(varIchi, 2) '１１．差込情報をセットする ActiveWorkbook.Sheets(1).Range(varIchi(1, j)) = varData(i, j + 2) Next End If '１２．指定されている作成ファイル名でブックを保存する ActiveWorkbook.SaveAs folder & "\" & varData(i, 1) & ".xlsx" Next '１３．新しいブックを閉じる ActiveWorkbook.Close False MsgBox "完了" End Sub |
４．ひな形シートをコピーして新規ブックを作成する機能の使い方
それでは、ひな形シートをコピーして、新規ブックを作成する方法を説明します。
まず、「メイン」シートのA2セルに「作成先フォルダパス」を入力します。
次に、B2セルに利用する「ひな形シート名」を入力します。
そして、A列・B列の4行目以降に「作成ファイル名」、「シート名」を入力します。
また、C列からL列の3行目に差込位置のセル番地を入力します。
最後に、C列からL列の4行目以降にそれぞれの差込位置に入力したい情報を入力します。
ひな形シートをコピーして新規ブックを作成する機能の使い方
次に、「開発」タブの「マクロ」をクリックします。
実行ボタンをクリックする
先程作成したVBAプログラム「MainProc」が一覧に表示されていますので、選択します。
次に、「実行」ボタンをクリックして新規ブック作成を開始します。
少し待つと、ひな形シートをコピーして、新規ブックが配布先リスト分作成されます。
ひな形シートをコピーして新規ブックが作成されました。
それでは、作成された新規ブック（001_人事部_山田.xlsx）の中身を見てみましょう。
作成された新規ブックの中身です。
指定されたひな形シートに指定されたシート名、差込情報が入力されています。
具体的には、部署名・従業員番号・氏名が指定された値で入力されています。
どうですか、簡単でしょ。
もっと大量のデータでも、同じく簡単ですのでやってみてください。
また、好きなひな形シートで新規ブックを作成してみましょう。
５．ひな形シートをコピーして新規ブックを作成するVBAプログラムの説明
それでは、VBAプログラムを説明します。
①「メイン」シートを変数に格納する
|
1 2 |
'１．「メイン」シートを変数に格納する Set shtMain = ThisWorkbook.Sheets("メイン") |
そうすることにより、入力するプログラムを短くすることができるからです。
何度も「ThisWorkbook.Sheets(“メイン”)」と入力するより、「shtMain」と入力するほうが少なくてすむからです。
②作成先フォルダパスを変数に格納する
|
1 2 |
'２．作成先フォルダパスを変数に格納する folder = shtMain.Cells(2, 1) |
③ひな形シート名を変数に格納する
|
1 2 |
'３．ひな形シート名を変数に格納する hinaName = shtMain.Cells(2, 2) |
④データ入力されているA列の最終行を取得する
|
1 2 |
'４．データ入力されているA列の最終行を取得する maxRow = shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row |
新規ブックを作成する数を管理するために、取得しています。
⑤データ入力されている3行目の最終列を取得する
|
1 2 |
'５．データ入力されている3行目の最終列を取得する maxCol = shtMain.Cells(3, shtMain.Columns.Count).End(xlToLeft).Column |
差込したい項目数を管理するために、取得しています。
⑥差込位置情報を取得する
|
1 2 3 4 |
'６．差込位置情報を取得する If maxCol > 2 Then varIchi = shtMain.Range(shtMain.Cells(3, 3), shtMain.Cells(3, maxCol)) End If |
ただし、3行目の最終列が2より大きい場合のみ、実行させています。
なぜなら、差込位置情報が入力されていない場合、「差込位置情報」を取得する必要がないからです。
⑦作成ファイル情報を取得する
|
1 2 |
'７．作成ファイル情報を取得する varData = shtMain.Range(shtMain.Cells(4, 1), shtMain.Cells(maxRow, maxCol)) |
⑧ひな形シートを変数に格納する
|
1 2 |
'８．ひな形シートを変数に格納する Set shtHina = ThisWorkbook.Sheets(hinaName) |
⑨作成するファイルを開く
|
1 2 |
'９．指定されたひな形シートを新しいブックへコピーする shtHina.Copy |
⑩シート名を変更する
|
1 2 |
'１０．シート名を変更する ActiveWorkbook.Sheets(1).Name = varData(i, 2) |
⑪差込情報をセットする
|
1 2 |
'１１．差込情報をセットする ActiveWorkbook.Sheets(1).Range(varIchi(1, j)) = varData(i, j + 2) |
⑫指定されている作成ファイル名でブックを保存する
|
1 2 |
'１２．指定されている作成ファイル名でブックを保存する ActiveWorkbook.SaveAs folder & "\" & varData(i, 1) & ".xlsx" |
拡張子は、固定で「xlsx」で保存しています。
⑬新しいブックを閉じる
|
1 2 |
'１３．新しいブックを閉じる ActiveWorkbook.Close False |
以上です。
それでは、ひな形シートをコピーして新規ブックを大量に作成しましょう。
これで、面倒なコピー作業や入力作業から解放されます。
最後まで読んでいただきありがとうございました。
この記事をシェアしていただけると喜びます。
