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

 

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

 

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

 

結論から言うと、シート名を取得する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を理解すれば編集したいシートが増えても簡単に対応できるようになります。

 

 

ユーザーフォーム内で直接マクロを記載すると、

マクロのデバック(間違っている箇所を修正する作業)がとてもやりにくい！

というデメリットがあります。

 

｢F8｣キーまたは｢F5｣キーでマクロのチェックをしようとすると毎回ユーザーフォームが起動しうまくマクロを実行できない！という事象が起こります。

ちょっとイメージしにくいとは思いますが、ユーザーフォームのコマンドボタンにはCallによるマクロの呼び出しが絶対におすすめです。

 

マクロをデバックする方法について下記の記事で詳しく紹介しています。