【エクセルマクロ】別シートに情報を自動反映させるマクロの作り方・導入方法を紹介
シートから別シートに必要な情報を転記する方法ってないの？
こんな疑問にお答えします。
結論から言うと、シート名を取得するVBAを使って、別シートに情報を自動反映させるマクロを作成する！です。
別シートに情報を転記するマクロの概要がこちら
一覧表に入力している大量の情報を、ボタンをクリックするだけで別シートの好きな場所(セル)に転記することが可能。
この別シートに情報を転記するマクロはシート名を取得する方法さえ理解すれば簡単に作成することができます。
この記事では画像や動画を使ってVBA初心者の方でも簡単に出来る転記マクロの作り方を紹介します。
エクセル業務の効率をアップさせたい！編集作業を自動化して楽したい！と考えている方は是非参考にしてみてください。
目次
別シートに情報を自動反映させるマクロとは？
「転記」とは別シートに情報を自動反映させることをいい、この「転記」を自動化するプログラムを「転記マクロ」と言っています。
下記の動画では、転記マクロがどのような処理をするのか！をわかりやすく紹介しているので参考にしてみてください。
動画では簡単な書類作成を例に紹介していますが、編集項目が多くなればなるほど作業時間の短縮や入力ミスの防止につながります。
転記マクロのメリットがこちら
メリット・記入事項の変更忘れ防止
・作業時間の短縮
・セル内の数式が壊される心配がない
・図形の配置も自由自在
下図のように、複数の情報一瞬で転記することができるので編集スピードを格段にアップさせることが可能です。
私の職場では、「工事契約書」の編集に使っているエクセルファイルに転記マクロを設定しています。
ガッツポーズの人
さらに応用すれば、
「一覧表」シートに入力した情報を「契約書」シート、「請求書」シート、「領収書」シートなど、複数のシートに複数の情報を転記することだって可能
です。
転記マクロの導入手順
マクロの導入手順を「工事契約書の編集に導入した転記マクロ」をサンプルとして詳しくご紹介していきいます。
様式の設定
「一覧表」シートの様式がこちら
「工事契約書」シートの様式がこちら
様式の設定は業務内容によって異なりますが、マクロのコードを書き始める前にしっかり作り込む必要があります。
なぜなら、コードを書き始めた後に様式の変更をしようとした場合コードを初めから作り直すことになりかねません。
ガッツポーズの人
VBAの設定
①VBA編集画面を開く
②Module1を作成
③Module1にコードを入力
Module1に入力するサンプルコードはこちら
Sub 工事契約書転記()
''''''''''''''''''''''''''Sheetの設定""""""""""""""""""""""""""""
Dim Ash As Worksheet
Dim Bsh As Worksheet
Set Ash = ThisWorkbook.Worksheets("一覧表")
Set Bsh = ThisWorkbook.Worksheets("工事契約書")
''''''''''''''''''''''''''ユーザーフォームで入力した値を変数iで取得""""""""""""""""""""""""""""
i = UserForm1.TextBox1.Value
''''''''''''''''''''''''''一覧表から工事契約書へ値を転記する""""""""""""""""""""""""""""
'''''工事名を転記'''''
Bsh.Range("H6") = Ash.Cells(i + 2, 2)
Bsh.Range("H6").HorizontalAlignment = xlLeft
'''''工事場所を転記'''''
Bsh.Range("H8") = Ash.Cells(i + 2, 3)
Bsh.Range("H8").HorizontalAlignment = xlLeft
'''''工期(着手)を転記'''''
Bsh.Range("H10") = Ash.Cells(i + 2, 4)
Bsh.Range("H10").HorizontalAlignment = xlCenter
Bsh.Range("H10").NumberFormatLocal = "ggge年m月d日"
'''''工期(竣功)を転記'''''
Bsh.Range("P10") = Ash.Cells(i + 2, 5)
Bsh.Range("P10").HorizontalAlignment = xlCenter
Bsh.Range("P10").NumberFormatLocal = "ggge年m月d日"
'''''請負金額を転記'''''
Bsh.Range("L12") = Ash.Cells(i + 2, 6)
Bsh.Range("L12").HorizontalAlignment = xlCenter
Bsh.Range("L12").NumberFormatLocal = "#,###"
'''''消費税を転記'''''
Bsh.Range("R14") = Bsh.Range("L12").Value * 0.1
Bsh.Range("R14").HorizontalAlignment = xlCenter
Bsh.Range("R14").NumberFormatLocal = "#,###"
'''''発注者住所を転記'''''
Bsh.Range("J33") = Ash.Cells(i + 2, 8)
Bsh.Range("J33").HorizontalAlignment = xlLeft
'''''発注者氏名を転記'''''
Bsh.Range("J35") = Ash.Cells(i + 2, 7)
Bsh.Range("J35").IndentLevel = 2
'''''受注者住所を転記'''''
Bsh.Range("J37") = Ash.Cells(i + 2, 10)
Bsh.Range("J37").HorizontalAlignment = xlLeft
'''''受注者氏名を転記'''''
Bsh.Range("J39") = Ash.Cells(i + 2, 9)
Bsh.Range("J39").IndentLevel = 2
End Sub上記サンプルコードをModule1にコピペしてください。
④ユーザーフォームの作成
⑤ユーザーフォーム(ツール)の作成
⑥ユーザーフォーム(コード)の作成
UserForm1に入力するサンプルコードはこちら
Private Sub CommandButton1_Click()
Call 工事契約書転記
End Sub
⑦Sheet1にコードを入力
「一覧表」シートを表示させたときユーザーフォームを表示させない。という内容です。
⑧Sheet2にコードを入力
「工事契約書」シートを表示させたときユーザーフォームを表示する。という内容です。
以上でVBAの設定は完了です。
ユーザーフォームを使って転記マクロを起動させる仕組みを採用しているのでVBAの設定が少し複雑になってしまいましたが、
手順通り操作していけば確実に転記マクロを導入できるはずです。
ガッツポーズの人
転記マクロに使われているVBA
転記マクロに使われているVBAをそれぞれ詳しくご紹介してきます。
シート名を取得するVBA
転記マクロにはシート名を取得するVBAが必要不可欠です。
シート名を取得することによってブック内のシートを区別することができるようになり、
どのシートに何の情報を表示させるのか
をVBAで表現することができるようになります。
シート名の取得方法は、
シートを代入するための変数を宣言し、変数に好きなシート名を格納する
です。
基本構造
| 1 | Dim 〇〇 As Worksheet |
| 2 | Set 〇〇 = Sheets(△△) |
〇〇に扱いやすい変数を入れ、△に数字またはSheet名を入れるだけ！
たったこれだけでシートの宣言が完了です。
上を目指す人
工事契約書に導入した転記マクロ内のシートを区別するVBAがこちら
Dim Ash As Worksheet
Dim Bsh As Worksheet
Set Ash = ThisWorkbook.Worksheets("一覧表")
Set Bsh = ThisWorkbook.Worksheets("工事契約書")
VBAを記入していく中で「一覧表シートはAsh」「工事契約書シートはBsh」と表現する
という内容です。
Set とは Setステートメントのことで、オブジェクト変数に値を入れたいときに Set を前に記載して使用します。
オブジェクト変数はワークブック、ワークシート、セルなどのことで、「～の場所」というイメージです。
シート名を取得するVBAとセルにテキストを入力するVBAを組み合わせることで転記VBAを作成することができます。
別シートに情報を転記させるマクロと聞けば難しい関数や数式が必要なのかと感じるかもしれませんが、難しいコードは一切必要ありません。
いくつかのサンプルを使って転記マクロについて紹介してきます。
転記マクロの基本構造(サンプル１)
Dim Ash As WorkSheet
Set Ash = Sheet(1)
Dim Bsh As WorkSheet
Set Bsh = Sheet(2)
Ash.cells( 1 , 1 )＝Bsh.cells( 1 , 1 )
上記のVBAは、
1番左側にあるシートのA1セルに左から2番目にあるシートのA1セルを転記する
というマクロです。
1行目から4行目でシート名の設定をしたあとは転記したい項目の箇所を繰り返し作り込むだけで転記プログラムの完成です。
転記マクロの基本構造(サンプル2)
Dim Ash As WorkSheet
Set Ash = Sheet(1)
Dim Bsh As WorkSheet
Set Bsh = Sheet(2)
Dim Csh As WorkSheet
Set Csh = Sheet(3)
Ash.cells( 1 , 1 )＝Csh.cells( 1 , 1 )
上記のVBAは、
1番左側にあるシートのA1セルに左から3番目にあるシートのA1セルを転記する
というマクロです。
シート名を区別するVBAを理解すれば編集したいシートが増えても簡単に対応できるようになります。
ユーザーフォームを使ったマクロの実行
サンプルで紹介している転記マクロでは、
ユーザーフォームのコマンドボタンとテキストボックスを使ってマクロを実行する
方法を採用しています。
コマンドボタンとテキストボックスにの設定方法を解説していきます。
コマンドボタン
コマンドボタンはボタンをクリックするとモジュールで作成したプログラムを呼び出す！という処理ができます。
基本構造(コードを直接入力するパターン)
| 1 | Private Sub CommandButton() |
| 2 | ～ここにプログラムを記入～ |
| 3 | End Sub |
上記のような方法でもコマンドボタンの実行は可能ですが、
直接コードを入力するのではなくCallで標準モジュールで作成したマクロを呼び出す方法
をおすすめします。
基本構造(Callを使ってマクロを呼び出すパターン)
| 1 | Private Sub CommandButton() |
| 2 | Call マクロ名 |
| 3 | End Sub |
Callを使うことで「マクロの修正が簡単になる」「複雑なマクロをキレイにまとめられる」というメリットがあります。
ユーザーフォーム内で直接マクロを記載すると、
マクロのデバック(間違っている箇所を修正する作業)がとてもやりにくい！
というデメリットがあります。
｢F8｣キーまたは｢F5｣キーでマクロのチェックをしようとすると毎回ユーザーフォームが起動しうまくマクロを実行できない！という事象が起こります。
ちょっとイメージしにくいとは思いますが、ユーザーフォームのコマンドボタンにはCallによるマクロの呼び出しが絶対におすすめです。
マクロをデバックする方法について下記の記事で詳しく紹介しています。
併せてチェックしてみてください。
テキストボックス
テキストボックスで入力した値をモジュールで作成したマクロで使用することができます。
どういうことかというとこんな感じ
標準モジュールのマクロで「変数i」に「ユーザーフォームのテキストボックスに入力された値」を格納するという意味です。
工事書類を作成する転記マクロでは変数 i を工事番号（「一覧表シート」の行）で使用しています。
別シートに情報を自動反映させるマクロを使ってみたいという方へ
転記マクロを使ってみたい！転記マクロを導入してエクセル業務の効率をアップさせたい！という方のために、
転記マクロ導入する２つの方法をご紹介します。
転記VBAのダウンロード
１つ目の導入方法は、
この記事で紹介している工事契約書に設定された転記マクロをダウンロードして使う
です。
ダウンロードしたエクセルファイルで、様式に編集しコードを書き直すことで業務内容に合わせた転記マクロに改良することが可能です。
転記VBAの作成依頼
２つ目の導入方法は、
マメBlogに転記マクロの開発依頼をする
です。
依頼方法は
どのような転記マクロを設定したいのか！こんなエクセル作業を転記マクロを使って自動化したい！
のようにザックリとした内容を伝えるだけ。
ガッツポーズの人
VBAを編集するのは難易度が高い！VBAを勉強して転記マクロを設定する時間がない！
このような方にマクロ開発依頼はとてもおすすめです。
下記の送信フォームからまずはお気軽にご相談ください。
別シートに情報を自動反映するマクロは仕事でかなり使わている！
別シートに情報を転記するマクロはいろんなエクセル業務に導入することが可能です。
上を目指す人
私の職場では工事契約書の編集以外に、「請求書の作成」「設備の検査管理」「点検の申請書の作成」などいろんなエクセルファイルに設定されています。
以下のような処理が必要なとき転記マクロは効力を発揮します。
- 会社名や氏名など基本となる情報を入力が必要
- 決められた様式の編集が必要
- 複数のシートで同じ内容の編集が必要
仕事でよくエクセルを使ってデスクワークを行っている！という方は是非転記マクロの導入を検討してみてください。