【エクセルマクロ】別シートに情報を自動反映させるマクロの作り方・導入方法を紹介

シートから別シートに必要な情報を転記する方法ってないの？

こんな疑問にお答えします。

結論から言うと、シート名を取得するVBAを使って、別シートに情報を自動反映させるマクロを作成する！です。

シート名を取得する方法さえ理解すれば、別シートに情報を自動反映させるマクロは簡単に作成することができます。

この記事では、画像や動画を使って初心者の方でも簡単に出来る転記マクロの作り方を紹介します。

1 別シートに情報を自動反映させるマクロとは？
2 転記マクロの紹介
3 シート名を取得する方法
4 セルの内容を別シートに転記するマクロの作り方
- 4.1 転記マクロの基本構造(サンプル１)
- 4.2 転記マクロの基本構造(サンプル2)
5 別シートに情報を自動反映させるマクロの導入(サンプル紹介)
6 別シートに情報を自動反映させるマクロを使ってみたいという方へ
- 6.1 転記VBAのダウンロード
- 6.2 転記VBAの作成依頼
7 別シートに情報を自動反映するマクロは仕事でかなり使わている！
8 これからVBAを勉強をはじめるには

別シートに情報を自動反映させるマクロとは？

別シートに情報を自動反映させることを｢転記｣といいます。「別シートに情報を自動反映させるマクロ」では文章が長くなり読みづらくなってしまうので、この記事では｢転記マクロ」という言葉で紹介していきます。

転記マクロがどのようなものかというと、シート1の情報をシート2の指定した場所に転記する！というもの。この転記マクロを使えば、様式の決まった書類を作成や人物名や日付の変更忘れを防止することができます。

メリット・記入事項の変更忘れ防止

・作業時間の短縮

・セル内の数式が壊される心配がない

・図形の配置も自由自在

エクセル業務で決まった様式の書類を日付や人物名を変更して何度も作成する作業がある方にとって転記マクロはとてもおすすめです。

私の職場では、決められた様式の書類を何度も編集する作業が多いため、ほとんどのエクセルファイルにこの転記VBAが導入されています。転記マクロを導入することで得られるメリットは編集ミスによるやり直し作業の激減です。

転記VBAを導入する前は、繰り返し作業による日付の編集忘れや人物名の入力間違いが発生していました。こんな同じ作業を繰り返す書類作成業務に転記マクロの導入はおすすめです。

転記マクロの紹介

ユーザーフォームのテキストボックスに工事番号を入力しすると、Sheet1（一覧表）からSheet2（工事契約書）に値を転記する！というマクロです。下記の動画では、転記マクロがどのような処理をするのか！がわかりやすく紹介されています。

動画では簡単な書類を例に紹介していますが、編集項目が多くなればなるほど作業時間の短縮や入力ミスの防止につながります。

私の職場では過去のデータをすぐに表示できるという点が好評です。

転記マクロを導入する前は、過去に編集したエクセルファイルがどこにあるのかわからない！どんどんシートを増やして編集作業をしているとファイルが重くなる！という事象が発生していましたが、このようなデメリットも解消することができます。

シート名を取得する方法

転記マクロにはシート名を取得するVBAが必要不可欠です。シート名を取得することによってブック内のシートを区別することができるようになり、転記マクロを作成できるようになります。

シート名を取得するには、シートを代入するための変数を宣言する必要があります。

マクロの構造

1	Dim 〇〇 As Worksheet
2	Set 〇〇 = Sheets(△△)

〇〇に扱いやすい変数を入れ、△に数字またはSheet名を入れるだけ！たったこれだけでシートの宣言が完了です。

豆父ちゃん

私は扱いやすいように〇〇には「Ash」や「Bsh」といった短い変数を入力するようにしています。

Set 〇〇 = Sheets(△△) の Set ってなんだろうと疑問に思う方が多いのではないでしょうか。

Set とは Setステートメントのことで、オブジェクト変数に値を入れたいときに Set を前に記載して使用します。

オブジェクト変数はワークブック、ワークシート、セルなどのことです。「～の場所」と考えるとイメージがつきやすいと思います。

	"’Ashという変数を宣言"’
1	Dim Ash As WorkSheet
	"’Ashに1番左にあるSheetを入れる"’〇〇 As Worksheet
2	Set Ash = Sheet(1)

上記プログラムは、1番左側にあるシート名を｢Ash｣と設定する！という意味です。

オブジェクト変数やSetステートメントは理解するのに時間がかかると思いますが、しっかり覚えておきましょう。

VBAでシート名を取得する方法については下記の記事で詳しく紹介しています。

mamemametochan.com

シート名を取得するVBAの設定【シートを区別することで作業効率アップ】

https://mamemametochan.com/excel-2

豆父ちゃんVBAでシート名を取得する方法が知りたい。こんな疑問にお答えします。結論からいうと、オブジェクト型変数を使ってシート名を宣言する！です。オブジェクト型変数と言われてもよくわからない！という方がほとんどですよね。実際のVBAではオブジェクト型変数を設定することになりますが、オブジェクト型変数を深く理解していなくてもできるシート名の取得方法をご紹介していきます。難しいことはいいから、VBAでシートを区別したい別シートに情報を転記するVBAを設定したいシート...

併せてチェックしてみてください。

セルの内容を別シートに転記するマクロの作り方

シート名を取得するVBAとセルにテキストを入力するVBAを扱うことができれば、転記VBAを作成することができます。

シートからシートに情報を転記させるマクロと聞けば難しい関数や数式が必要なのかと感じるかもしれませんが、難しいことは一切必要ありません。

いくつかのサンプルを使って実際に転記マクロを作成していきます。

転記マクロの基本構造(サンプル１)

1	Dim Ash As WorkSheet
2	Set Ash = Sheet(1)
3	Dim Bsh As WorkSheet
4	Set Bsh = Sheet(2)
5	Ash.cells( 1 , 1 )＝Bsh.cells( 1 , 1 )

上記のサンプル(1)は、1番左側にあるシートのA1セルに左から2番目にあるシートのA1セルを転記する！というマクロです。1行目から4行目でシート名の設定をしてしまえば、あとは転記したい項目の箇所を繰り返し作り込むだけで転記プログラムの完成です。

シート名の取得するVBAを増やすと操作できるシートを増やすことができます。

転記マクロの基本構造(サンプル2)

1	Dim Ash As WorkSheet
2	Set Ash = Sheet(1)
3	Dim Bsh As WorkSheet
4	Set Bsh = Sheet(2)
5	Dim Csh As WorkSheet
6	Set Csh = Sheet(3)
7	Ash.cells( 1 , 1 )＝Csh.cells( 1 , 1 )

上記のサンプル(2)は、1番左側にあるシートのA1セルに左から3番目にあるシートのA1セルを転記する！というマクロです。シート名を取得するVBAを扱えるようになると、編集したいシートが増えても簡単に対応できるようになります。是非使い方をマスターしましょう。

別シートに情報を自動反映させるマクロの導入(サンプル紹介)

サンプルマクロは動画で紹介している工事契約書類の作成に使われているものです。このマクロを導入してから編集のミスが減り作業効率が格段に上がりました。

標準モジュールに記載する転記マクロ

1	Sub 工事契約書転記()
2	""Sheetの設定""""
3	Dim Ash As Worksheet
4	Dim Bsh As Worksheet
5	Set Ash = ThisWorkbook.Worksheets(“一覧表")
6	Set Bsh = ThisWorkbook.Worksheets(“工事契約書")
7	"’ユーザーフォームで入力した値を変数iで取得"""
8	i = UserForm1.TextBox1.Value
9	"’一覧表から工事契約書へ値を転記する"""
10	""’工事名を転記""’
11	Bsh.Range(“H6") = Ash.Cells(i + 2, 2)
12	Bsh.Range(“H6").HorizontalAlignment = xlLeft
13	""’工事場所を転記""’
14	Bsh.Range(“H8") = Ash.Cells(i + 2, 3)
15	Bsh.Range(“H8").HorizontalAlignment = xlLeft
16	""’工期(着手)を転記""’
17	Bsh.Range(“H10") = Ash.Cells(i + 2, 4)
18	Bsh.Range(“H10").HorizontalAlignment = xlCenter
19	Bsh.Range(“H10").NumberFormatLocal = “ggge年m月d日"
20	""’工期(竣功)を転記""’
21	Bsh.Range(“P10") = Ash.Cells(i + 2, 5)
22	Bsh.Range(“P10").HorizontalAlignment = xlCenter
23	Bsh.Range(“P10").NumberFormatLocal = “ggge年m月d日"
24	""’請負金額を転記""’
25	Bsh.Range(“L12") = Ash.Cells(i + 2, 6)
26	Bsh.Range(“L12").HorizontalAlignment = xlCenter
27	Bsh.Range(“L12").NumberFormatLocal = “#,###"
28	""’消費税を転記""’
29	Bsh.Range(“R14") = Bsh.Range(“L12").Value * 0.1
30	Bsh.Range(“R14").HorizontalAlignment = xlCenter
31	Bsh.Range(“R14").NumberFormatLocal = “#,###"
32	""’発注者住所を転記""’
33	Bsh.Range(“J33") = Ash.Cells(i + 2, 8)
34	Bsh.Range(“J33").HorizontalAlignment = xlLeft
35	""’発注者氏名を転記""’
36	Bsh.Range(“J35") = Ash.Cells(i + 2, 7)
37	Bsh.Range(“J35").IndentLevel = 2
38	""’受注者住所を転記""’
39	Bsh.Range(“J37") = Ash.Cells(i + 2, 10)
40	Bsh.Range(“J37").HorizontalAlignment = xlLeft
41	""’受注者氏名を転記""’
42	Bsh.Range(“J39") = Ash.Cells(i + 2, 9)
43	Bsh.Range(“J39").IndentLevel = 2
44	End Sub

ユーザーフォームに記載するマクロ

1	Private Sub CommandButton1_Click()
2	Call 工事契約書転記
3	End Sub

1	Private Sub TextBox1_Change()
2	End Sub

1	Private Sub UserForm_Click()
2	End Sub

1	Private Sub UserForm_Layout()
2	UserForm1.Left = 750
3	UserForm1.Top = 200
4	End Sub

上記のプログラムはコピーしてそのまま使えるので、転記マクロの導入を検討している方は使ってみて下さい。

転記マクロの3行目から6行目でシートの設定をしています。「一覧表シート」には｢Ash｣、「工事契約書シート」には｢Bsh｣という変数を設定しています。転記マクロではセルの書式設定を行うことが重要です。文字の横位置や日付の表示や金額表示などの設定を行うことで見やすいキレイな書類を作成することができます。

サンプルの工事契約書類で使われているのは以下の書式設定です。

今回使用した書式設定