【エクセルマクロ】別シートに情報を自動反映させるマクロの作り方・導入方法を紹介
シートから別シートに必要な情報を転記する方法ってないの？
こんな疑問にお答えします。
結論から言うと、シート名を取得する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をアレンジする２つの方法
を具体的にご紹介します。
それは、
「VBAのコード開発を第三者に依頼する」または「自分でVBAのコードを編集する」
という方法です。
VBAの開発を依頼する
「VBAのコード開発を第三者に依頼する」は、
プログラミング開発を請け負っている会社、または個人に依頼することで
業務内容に合わせたVBAの導入ができます。
通常の業務に専念しつつエクセル業務の効率をアップできるのでめちゃくちゃおすすめの手段なのですが、
依頼先によっては費用が高い！担当者によって完成度の差が激しい
などのデメリットがあります。
勤めていた会社でよく利用していたのですが、
「これだけのVBAでこんなに請求されるの！？？」
と驚かされたことも。。。
疑問がある人
VBAのコード開発を依頼予定の方は、
依頼先の候補として「マメBlogのエクセルVBA開発サービス」
を検討してみてください。
どんなエクセル作業をしていて、どんなVBAを設定したいのか。
を聞き取りをしてVBAを開発。
上を目指す人
関連記事「【マクロ開発実績】年間50件の受注達成！」では、VBAの開発実績を詳しく紹介しているので参考にしてみてください。
マメBlogが提供するVBA開発サービスの特徴がこちら
- 現在行っている業務内容に合わせてVBAを設定
- 依頼を受けてから２週間以内に納入
- 簡単な改修は追加費用なしで実施
「実際に使っている様式」「職場での運用方法」に合わせてVBAを設定するので、
めちゃくちゃ使いやすいと大好評。
効率をアップさせたい！でもVBAを1から勉強する時間なんてない！！
こんな方は、是非「マメBlogVBA開発サービス」の利用を検討してみてください。
自分でVBAのコードを編集する
「自分でVBAのコードを編集する」にはVBAの勉強が必要です。
プログラミングの勉強って難しそぅ！
と思われるかもしれませんがそんなことはありません。
VBAは
「ネットや参考書で情報をゲットしやすい」「エクセルを使えればすぐにでもVBAを操作できる」
という理由から初心者が挑戦しやすいプログラミングだと言われています。
例えば、
【VBA 書き方】
【VBA 転記 サンプルコード】
このようにネット検索すればいろんな情報を手に入れることができて、開発しようとしているVBAの難易度をすぐ調べることができます。
簡単なVBAであれば、
ネットや参考書で紹介されいるコードを組み合わせて業務内容に合わせたVBAに仕上げる
ことが可能です。
ガッツポーズの人
実現させたいVBAの難易度を確認してから、
「自分でVBA開発を進めるのか」それとも「第三者にVBA開発を依頼するのか」
を検討すると効率よくVBAの導入が可能です。
今後のためにVBAの知識を身に付けて業務内容に合わせたVBAを自ら設定したい！
と考える方はエクセルVBAの情報を発信しているメルマガ登録がおすすめ。
メルマガでは、以下のような情報を不定期に発信しています。
- 普通の会社員がVBAの知識を身に付けた方法
- 業務内容に合わせたVBAの開発方法
- VBA開発依頼の中から「これはエクセル業務で使える！！」と個人的に感じたコードの紹介
参考書やプログラミングスクールでの授業で行うような
「変数の意味とは」「プロパティとは」
という解説は行いません。
というか、私はプログラマーではないのでできません。
効率の悪いエクセル業務を毎日、何時間も強いられた苦しい経験から得たスキルを使って
エクセル業務の効率がアップするVBAのコードや作り方の情報
を積極的に紹介しています。
プログラミング学習は内容が難しいため途中で挫折する人が多い！
こんな不安からVBA学習をスタートさせていない方は、まずは無料で始められるメルマガでVBAの情報をお得にゲットしてみてください。