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

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

 

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

 

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

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

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

 

広告

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

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

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

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

・作業時間の短縮

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

・図形の配置も自由自在

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

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

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

 

転記マクロの紹介

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

 

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

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

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

 

シート名を取得する方法

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

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

 

マクロの構造

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

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

 

Left Caption

豆父ちゃん

私は扱いやすいように〇〇には「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セルにテキストを入力するVBAを扱うことができれば、転記VBAを作成することができます。

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

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

 

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

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」という変数を設定しています。転記マクロではセルの書式設定を行うことが重要です。文字の横位置や日付の表示や金額表示などの設定を行うことで見やすいキレイな書類を作成することができます。

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

今回使用した書式設定

 

  • .HorizontalAlignment=xlCenter 中央揃え
  • .HorizontalAlignment=xlLeft 左揃え
  • .NumberFormatLocal="#,###" 3桁目にカンマ
  • .NumberFormatLocal="ggge年m月d日" 和暦

VBAを使ってセルの書式設定を行う方法について下記の記事で詳しく紹介しています。

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

 

VBAの実行方法の設定

VBAの実行方法として、ユーザーフォームが使われています。ユーザーフォームではテキストボックスとコマンドボタンが使われていて、一覧表の工事番号を入力して実行すると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=UserForm1.TextBox1.Valueという入力します。これはユーザーフォーム1のテキストボックス1の値を変数 i に代入するという意味です。

今回の転記マクロでは変数 i を工事番号(「一覧表シート」の行)で使用しています。

 

別シートに情報を自動反映させるマクロを使ってみたいという方へ

転記VBAのダウンロード

転記VBAを仕事やプライベートのパソコンで使ってみたい!という方のために、この記事で紹介している転記VBAが設定されているエクセルファイルを下記のサイトからダウンロードしてお使いいただけます。気になる方は是非参考にしてみてください。

転記VBAの作成依頼

転記VBAは使ってみたいけど、紹介されているシートの様式では全然使えない!と感じる方がほとんどだと思います。そんな方のために、当ブログではエクセルマクロの開発依頼を承っております。

使っている様式に合わせて転記するVBAを設定してほしい!転記VBAにこんな機能を追加してほしい!などのマクロの開発依頼の受注しています。作成依頼ではなく、転記マクロについての質問や意見のお問合せでも構いません。

気になる方は、下記の送信フォームから問い合わせ内容を記載して送信してください。

 

別シートに情報を自動反映するマクロは仕事でかなり使わている!

シート名を取得するだけでできる簡単転記マクロを紹介しました。やり方をマスターをすれば、多数のシートを対象とした転記マクロを作成することができます。このマクロは実際の業務でかなり使えるマクロなので自由に扱えるようにしておきましょう。

下記の記事では、VBA参考書の選び方やポイントについて詳しく紹介しています。この記事で紹介している「別シートに情報を自動反映するエクセルマクロ」もVBA参考書を使って開発したものです。

 

 

仕事の効率アップにつながるマクロを開発してみたい!と考えている方は是非参考書で読んでみて下さい。あなたの考えを表現するためのVBAがきっと見つかると思います。

参考書では、シートから別のブックに情報を自動反映させるマクロの作り方も掲載されています。

 

下記の記事では、他のブックを操作するマクロについて詳しく紹介しています。いろんなエクセル作業でよく使われるマクロなので是非参考にしてみてください。

 

 

シートからシートまたはブックからブックの転記マクロを導入することで、ミスが激減し、大幅な作業時間の削減に繋がります。転記マクロの導入を検討している方は参考にしてみてください。

 

これからVBAを勉強をはじめるには

転記VBAのように、VBAには仕事の効率をアップさせることができる機能がいろいろあります。VBAのプログラミングスキルを身に着けることで、今まで何時間もかかかっていた作業をほんの数分で処理することができるようになります。

VBAを基本から学びたい!という方におすすめなのが、オンラインプログラミング学習サービス【1st Step】です。

【1st Step】は初心者のためのプログラミング学習サービスで、C言語・java・VBAの3つのプログラミングの基礎をオンラインで勉強することができます。

 

  • 基礎からプログラミングを勉強したい
  • まとまった勉強時間の確保が難しい
  • 途中で挫折したくない

上記のように考えている方に【1st Step】はおすすめです。
下記の記事では、オンラインプログラミング学習サービス【1st Step】の特徴や私の体験など詳しく紹介しています。

これからVBAの勉強を始めようと考えている方は是非参考にしてみてください。