はじめに
本記事の対象者
- Excelのリストの内容を、別Excelファイルの定型フォーマットの対応するセルに転記したい人
- 大量の定型フォーマットの対象ファイルに転記したい人
本記事の目的
- いつも定型フォーマットに内容を転記しているが、効率的にやれるようにする目的
Excelマクロで別ファイルにリストの内容を転記する方法
実際の別ファイルにリストの内容を転記する流れ
今回ご紹介するマクロで実際にできることを転記前と転記後の図で解説します。
転記前の図は、下のようになります。
転記前は、転記元のリストに定型フォーマットに記入したい情報がリストアップされている状態になっています。
そのため、図の左下のように「日付」「氏名」のように必要な情報が記載されていることが分かります。
その情報を図の右の定型フォーマットのファイルに転記していくことになります。
その転記後の図は下のようになります。
図より、正常に値が反映されていることが分かります。
このように定型フォーマットに一度に書き込むことができるので、人手で何十個とファイルを開いているものをシステム的にやるので、今までやっていたことが10分以上短縮されるなどは、ざらにあると思います。
本記事ではそのようなマクロのご紹介をしたいと思いますので、最後まで読んでいただけたら幸いです。
別ファイルにリストの内容を転記するマクロ(VBA)
今回のご紹介するマクロは下のものになります。
Sub 転記マクロ()
Dim FileName As String ‘ファイル名を格納する変数
Dim index As Integer ‘情報を取得する行数を格納する変数
Dim wb As Workbook ‘ワークブックのオブジェクトを格納する変数
index = 5 ‘転記元で情報を取得する行数を設定
Do
‘転記先のファイル名を転記元から取得して、変数に設定
FileName = ThisWorkbook.Worksheets(1).Cells(index, 3).Value
‘取得する転記先のファイル名がなければ、ループから脱出
If FileName = “” Then
Exit Do
End If
‘ファイルを開く
Workbooks.Open ThisWorkbook.Path & “\” & FileName
‘転記元のファイルを変数に設定
Set wb = ActiveWorkbook
‘転記元から転記先へ情報を転記する
wb.Worksheets(1).Cells(3, 3).Value = ThisWorkbook.Worksheets(1).Cells(index, 4).Value
wb.Worksheets(1).Cells(4, 3).Value = ThisWorkbook.Worksheets(1).Cells(index, 5).Value
wb.Worksheets(1).Cells(9, 2).Value = ThisWorkbook.Worksheets(1).Cells(index, 6).Value
wb.Worksheets(1).Cells(9, 3).Value = ThisWorkbook.Worksheets(1).Cells(index, 7).Value
wb.Worksheets(1).Cells(9, 4).Value = ThisWorkbook.Worksheets(1).Cells(index, 8).Value
‘何かメッセージが出るような場合でも非表示にするように設定
Application.DisplayAlerts = False
‘転記先ファイルの変更を保存
wb.Close savechanges:=True
‘何かメッセージが出るような場合でも表示にするように設定
Application.DisplayAlerts = True
‘次の転記先ファイル名を取得するために、行数+1する
index = index + 1
Loop
MsgBox (“転記完了”)
End Sub
コピーして個々の設定個所を変更してもらえれば、使えます。
また、マクロ(VBA)と簡易なフローとの対応表を作成したので、少しでも理解を深める助けになればと思います。
ここから下からは、コメントにも書いてはあるのですが、より詳しい説明を書き足していきます。
Excelマクロの別ファイルにリストの内容を転記する各処理の説明
処理の細かい説明をしようと思いますが、一部これは説明しなくてもいいと思っているところは割愛しています。
ご理解の程よろしくお願いいたします。
処理1．変数の定義
今回使用する変数は以下の3つになります。
- FileName：ファイル名を格納する変数
- index：情報を取得する行数を格納する変数
- wb：ワークブックのオブジェクトを格納する変数
変数の定義の該当箇所は下の部分です。
Dim FileName As String ‘ファイル名を格納する変数
Dim index As Integer ‘情報を取得する行数を格納する変数
Dim wb As Workbook ‘ワークブックのオブジェクトを格納する変数
変数の名前は各自が分かりやすい名前を付けていただければと思います。
処理２．転記先のファイル名を転記元から取得して、変数に設定
次に転記先のファイル名を転記元のリストから抽出して、変数FileNameに格納する。
その該当箇所は、以下のところです。
‘転記先のファイル名を転記元から取得して、変数に設定
FileName = ThisWorkbook.Worksheets(1).Cells(index, 3).Value
上記の内容の概念図は以下の通りになります。
ファイル一覧自体の作成方法については以下の記事を参考にしてください。
worksheets(1)は、「転記元_リスト.xlsm」のファイルの中のシートで、一番左のシートを指しています。
そのため、もしシートが複数ある場合は、左から何番目のシートかを数えた上で数を変更してください。
処理３．取得する転記先のファイル名がなければ、ループから脱出
取得する転記先のファイル名がなければ、ループから脱出するための処理は以下のようになります。
If FileName = “” Then
Exit Do
End If
上記は「処理２．」の変数FileNameに設定した値が、（ブランク）になっているかを判定します。
その判定結果により以下の処理をします。
- 変数FileNameの値が(ブランク)の場合、ループを脱出
- 変数FileNameの値が(ブランク)でない場合、処理を継続
「処理２．」では、ファイル名が記載されている「C列」を順々に上から見ていくので、いずれかはファイル名が記載されていないセルに当たります。
そのファイル名が記載されていないセルにあった際に、処理を終了するためにこのような処理を入れて入れてします。
処理4．転記元から転記先へ情報を転記する
ここが一番重要な部分になります。
転記元のリストの情報を転記先の所定の箇所に転記する処理は以下のようになります。
‘転記元から転記先へ情報を転記する
wb.Worksheets(1).Cells(3, 3).Value = ThisWorkbook.Worksheets(1).Cells(index, 4).Value
wb.Worksheets(1).Cells(4, 3).Value = ThisWorkbook.Worksheets(1).Cells(index, 5).Value
wb.Worksheets(1).Cells(9, 2).Value = ThisWorkbook.Worksheets(1).Cells(index, 6).Value
wb.Worksheets(1).Cells(9, 3).Value = ThisWorkbook.Worksheets(1).Cells(index, 7).Value
wb.Worksheets(1).Cells(9, 4).Value = ThisWorkbook.Worksheets(1).Cells(index, 8).Value
転記元のリストと転記先のセルの対応を図でいうと下のようになります。
補足としてセルの情報とかも追加していて、見比べていただければなんとなくわかってくると思います。
重要な部分はリストの「Cells(index, 4).Value」と転記先である「Cells(X, X).Value」の対応がちゃんとできているかです。
この対応がちゃんとしていれば、定型フォーマットにどんどん記入できるようになります。
説明は以上となります。
補足情報 今回の処理の想定用途
今回の処理の用途は、同じ定型フォーマットのファイルが大量にあり、手元に転記すべき情報はあって張り付けいく作業の効率化です。
本当に決まっているものであれば、たぶん1時間とかかけていたものが5分程度で終わるようになるとは思います。(あくまでも個人の感想ですが。。。)
まとめ
まとめに入ります。
Excelマクロで別ファイルにリストの内容を転記する方法を説明しました。
大量の定型フォーマットのファイルに書き込むだけの作業が一瞬で終わるようになるはずです。
個人的には以下のように他の記事でPowerAutomateを利用したフローの紹介をしているので、PowerAutomateのRPA機能を使って記事を書いてみたいと思います。
最後まで読んでいただきありがとうございます。
コメント