VBAを使うとセルに入力したデータを別シートに蓄積させていくことができます。
今回はSheet1に入力したデータを、Sheet2の最終行に1行ずつ蓄積していくプログラムを紹介します。
・別シートの最終行へデータを蓄積
・VBA入りのエクセルファイルをダウンロード可能
それでは以下で詳しく紹介していきます。
目次
エクセルマクロVBA｜セルにデータ入力して別シートに自動で蓄積させる
今回は以下の手順で、データ入力したシートから別シートへ蓄積させるプログラムを作っていきます。
手順1. エクセルにデータ入力
手順2. VBAプログラムを実行（別シートへデータ蓄積）
手順3. VBAプログラムを実行（データ削除）
以下で詳しく説明していきます。
手順1. エクセルにデータ入力
エクセルにデータ入力します。
この事例では、Sheet1のC2~C7にデータを入力します。
これらのデータを別シートへ蓄積していきます。
手順2. VBAプログラムを実行（別シートへデータ蓄積）
「登録ボタン」を押下して、VBAを実行します。
Sheet1のデータをSheet2の最終行の1行下へ蓄積していきます。
このとき、以下のデータを蓄積していきます。
B列：会社名 → Sheet1のデータを出力
C列：郵便番号 → Sheet1のデータを出力
D列：住所 → Sheet1のデータを出力
E列：電話番号 → Sheet1のデータを出力
F列：担当者 → Sheet1のデータを
G列：メールアドレス → Sheet1のデータを出力
H列：登録日 → マクロ実行時の日付を自動出力
このようにA,H列はプログラムで生成し、B~G列はSheet1のデータを蓄積する形にしています。
手順3. VBAプログラムを実行（データ削除）
VBAを実行して、手順1で入力したデータを削除します。
「クリア」ボタンを押下すると、データを一括削除します。
データ削除のプログラムは別のページで紹介しますので、そちらをご覧ください。
ボタンにVBAプログラムを登録
上記の画像では、VBAプログラムをボタンに登録しています。
ボタンにVBAプログラムを登録することで、ボタンを押下しプログラムを実行することができます。
ボタンをVBAプログラムを設定したい場合は、以下で動画も交えて設定方法を紹介しているので、そちらをご覧ください。
VBA入りのエクセルファイルをダウンロード
以下で紹介しているVBAプログラムをそのまま使いたい人は、以下のフォームからダウンロードできます。
登録したメールアドレスへ「VBAプログラムが含まれたエクセル」を送信します。
本プログラムの内容をそのまま使用可能です。ぜひお仕事にお役立てください。
それでは、以下でプログラムについて詳細を説明します。
VBA作成前の事前準備
ExcelでVBAを使うためにはいくつかの準備が必要です。
具体的には以下です。
以下で説明します。
1. ExcelでVBAを使うための事前準備
エクセルVBAを使うためには、保存ファイルの拡張子変更、Excelの基本設定変更の2つをしておきます。
2つともで難しい作業はなく、それぞれ1分もあれば設定変更可能です。
上記に関しては、以下の記事で解説をしています。
もしVBAを使うための準備段階に不安がある人は上記をご覧ください。
この内容は以下の動画で紹介しています。
入門エクセルマクロの使い方｜マクロ作成から実行までを徹底解説
文字や画像だけで分かりづらい人は上記の動画をご覧ください。
VBAのプログラムソース解説
今回紹介するプログラムの概要は以下です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
'プログラム0｜変数宣言の指定 Option Explicit 'プログラム1｜プログラム開始 Sub DataToOtherSheet() 'プログラム2｜シート設定 Dim ws1 As Worksheet, ws2 As Worksheet Set ws1 = ThisWorkbook.Worksheets("Sheet1") Set ws2 = ThisWorkbook.Worksheets("Sheet2") 'プログラム3｜ws1のC2～C7の値を取得 Dim kaisya As String, yubin As String, jusyo As String Dim tel As String, tanto As String, mailaddress As String kaisya = ws1.Range("C2").Value yubin = ws1.Range("C3").Value jusyo = ws1.Range("C4").Value tel = ws1.Range("C5").Value tanto = ws1.Range("C6").Value mailaddress = ws1.Range("C7").Value 'プログラム4｜未入力のセルがあればflag=Trueとする Dim flag As Boolean flag = False If kaisya = "" Then: flag = True If yubin = "" Then: flag = True If jusyo = "" Then: flag = True If tel = "" Then: flag = True If tanto = "" Then: flag = True If mailaddress = "" Then: flag = True 'プログラム5｜未入力セルがあればプログラムを終了させる If flag = True Then MsgBox "未入力セルがあるので、入力すること" Exit Sub End If 'プログラム6｜ws2の最終行を取得 Dim cmax As Long cmax = ws2.Range("A65536").End(xlUp).Row 'プログラム7｜ws2の最終行の1行下にデータを蓄積 ws2.Range("A" & cmax + 1).NumberFormatLocal = "@" ws2.Range("A" & cmax + 1).Value = Format(cmax, "000") ws2.Range("B" & cmax + 1).Value = kaisya ws2.Range("C" & cmax + 1).Value = yubin ws2.Range("D" & cmax + 1).Value = jusyo ws2.Range("E" & cmax + 1).Value = tel ws2.Range("F" & cmax + 1).Value = tanto ws2.Range("G" & cmax + 1).Value = mailaddress ws2.Range("H" & cmax + 1).Value = Date 'プログラム8｜エクセルブックを上書き保存 ThisWorkbook.Save 'プログラム9｜プログラム終了 End Sub |
以下で詳しく説明しています。
プログラム0｜変数宣言の指定
|
1 |
Option Explicit |
「Option Explicit」とは、変数宣言を強制するためのものです。
予期しないエラーを防止できるため「Option Explicit」を入力することを習慣化することを推奨しています。
詳しい説明は以下のページで紹介しています。
プログラム1｜プログラム開始
|
1 |
Sub DataToOtherSheet() |
「Sub DataToOtherSheet()」のプログラムを開始することを意味します。
VBAではプロシージャという単位でプログラムを呼び出します。
プロシージャの構文は下記となっています。
|
1 2 3 |
Sub プロシージャ名() '実行プログラム End Sub |
「Sub」で始まり「End Sub」で終わります。
「Sub XXXX」の「XXXX」の部分がプロシージャ名です。
このプロシージャ名はあらゆる文字（アルファベット、ひらがな、漢字、数字など）が使用可能です。
ただし、プロシージャ名の先頭は数字を入れるとエラーとなります。
あとで見たときに、「何のプログラムだったのか？」とならないようにするためです。
なお、「()」の中には何も記入されていません。これは引数なしという意味です。なお、プログラム2-1では引数を受け取ってプログラムを実行します。
初心者の内は、引数ということが分からなくてもVBAプログラムを書くことは可能です。
興味があれば、「VBA 引数」で検索して調べてみてください。
プログラム2｜シート設定
|
1 2 3 |
Dim ws1 As Worksheet, ws2 As Worksheet Set ws1 = ThisWorkbook.Worksheets("Sheet1") Set ws2 = ThisWorkbook.Worksheets("Sheet2") |
変数ws1とws2をWorksheet(ワークシート)型で定義し、「Sheet1」シートと「Sheet2」シートを設定します。
なお「ThisWorkbook」を付けることで、VBAプログラムが含まれるエクセルファイルの「Sheet1」と「Sheet2」を細かく指定しています。
プログラム3｜ws1のC2～C7の値を取得
|
1 2 3 4 5 6 7 8 |
Dim kaisya As String, yubin As String, jusyo As String Dim tel As String, tanto As String, mailaddress As String kaisya = ws1.Range("C2").Value yubin = ws1.Range("C3").Value jusyo = ws1.Range("C4").Value tel = ws1.Range("C5").Value tanto = ws1.Range("C6").Value mailaddress = ws1.Range("C7").Value |
使用する変数を設定し、ws1(Sheet1)のC2~C7のセルの値をそれぞれ変数として設定します。
変数設定に関しては、以下のページで説明していますので、そちらをご覧ください。
セルの値を変数として取得
セルC3：郵便番号 → yubinで取得
セルC4：住所 → jusyoで取得
セルC5：電話番号 → telで取得
セルC6：担当者 → tantoで取得
セルC7：メールアドレス → mailaddressで取得
上記の値を変数として取得します。
Debug.Printでの検証結果
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
kaisya = ws1.Range("C2").Value yubin = ws1.Range("C3").Value jusyo = ws1.Range("C4").Value tel = ws1.Range("C5").Value tanto = ws1.Range("C6").Value mailaddress = ws1.Range("C7").Value Debug.Print "kaisya:" & kaisya Debug.Print "yubin:" & yubin Debug.Print "jusyo:" & jusyo Debug.Print "tel:" & tel Debug.Print "tanto:" & tanto Debug.Print "mailaddress:" & mailaddress >>>kaisya:aaa >>>yubin:000-0000 >>>jusyo:東京都中央区日本橋0-0-0 XXXビル1階 >>>tel:00-0000-0000 >>>tanto:山下 >>>mailaddress:fastclassinfo@gmail.com |
各セルの値をそれぞれの変数で取得することができました。
プログラム4｜未入力のセルがあればflag=Trueとする
|
1 2 3 4 5 6 7 8 |
Dim flag As Boolean flag = False If kaisya = "" Then: flag = True If yubin = "" Then: flag = True If jusyo = "" Then: flag = True If tel = "" Then: flag = True If tanto = "" Then: flag = True If mailaddress = "" Then: flag = True |
変数flagをBoolean型で取得し、flagをFalseとします。
ちなみにBoolean型の変数は初期値がFalseですが、敢えて分かりやすくするためにflag=Falseとして明示しています。
ここではIf文を使って、セルC2~C7の入力値に空欄があるかどうかを判定しています。
もしC2~C7の値を入れた変数のどれかに空欄があった場合、flag=Trueとなります。
if文については以下で事例を使って説明していますので、そちらをご覧ください。
プログラム5｜未入力セルがあればプログラムを終了させる
|
1 2 3 4 |
If flag = True Then MsgBox "未入力セルがあるので、入力すること" Exit Sub End If |
プログラム4でflagがTrueになった場合、このプログラム5が実行されます。
MsgBoxでメッセージが表示され、Exit Subでプログラムを終了させます。
プログラム6｜ws2の最終行を取得
|
1 2 |
Dim cmax As Long cmax = ws2.Range("A65536").End(xlUp).Row |
ws1のA列の最終行をcmaxとして取得します。
Debug.Printの検証結果
|
1 2 3 4 5 6 |
Dim cmax As Long cmax = ws2.Range("A65536").End(xlUp).Row Debug.Print cmax >>>cmax:1 |
Sheet2(ws2)のA列の最終行はA1なので、cmax=1となります。
プログラム7｜ws2の最終行の1行下にデータを蓄積
|
1 2 3 4 5 6 7 8 9 |
ws2.Range("A" & cmax + 1).NumberFormatLocal = "@" ws2.Range("A" & cmax + 1).Value = Format(cmax, "000") ws2.Range("B" & cmax + 1).Value = kaisya ws2.Range("C" & cmax + 1).Value = yubin ws2.Range("D" & cmax + 1).Value = jusyo ws2.Range("E" & cmax + 1).Value = tel ws2.Range("F" & cmax + 1).Value = tanto ws2.Range("G" & cmax + 1).Value = mailaddress ws2.Range("H" & cmax + 1).Value = Date |
Sheet2(ws2)の最終行の1行下にデータを蓄積していきます。
「cmax+1」で最終行の1行下を指定できる
プログラム6で取得した最終行の1つ下の行にデータを蓄積していくことを想定しています。
そのため、「cmax+1」で行を指定するようにしています。
これにより最終行の1行下にデータを出力することができます。
顧客IDを3桁の数値(0詰め)とする
|
1 2 |
ws2.Range("A" & cmax + 1).NumberFormatLocal = "@" ws2.Range("A" & cmax + 1).Value = Format(cmax, "000") |
Sheet2(ws2)のA列は顧客IDとしていて、Sheet1(ws1)にはデータがありません。
データを蓄積するときに顧客IDを付番しています。
ここでは001,002,003,・・・,というように3桁の数値(左0詰め)を顧客IDとしていきます。
このとき以下のプログラムだけだと、0が省略されてしまいます。
|
1 |
ws2.Range("A" & cmax + 1).Value = Format(cmax, "000") |
「NumberFormatLocal = “@”」で表示形式を変更
そこで「NumberFormatLocal = “@”」で、A列のセル表示形式を文字列に変更しています。
表示形式を変更した後で、Format(cmax,”000”)とすることで、3桁の数値(左0詰め)を顧客IDを取得することができます。
なおcmaxはデータが蓄積されるごとに1,2,3,・・・と1ずつ加算されていくため、cmaxをそのまま顧客ID生成に利用しています。
With~End Withを使うと少し記載が簡略化できる
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
With ws2 With .Range("A" & cmax + 1) .NumberFormatLocal = "@" .Value = Format(cmax, "000") End With .Range("B" & cmax + 1).Value = kaisya .Range("C" & cmax + 1).Value = yubin .Range("D" & cmax + 1).Value = jusyo .Range("E" & cmax + 1).Value = tel .Range("F" & cmax + 1).Value = tanto .Range("G" & cmax + 1).Value = mailaddress .Range("H" & cmax + 1).Value = Date End With |
With~End Withを2回使って、共通部分を括りだすことができます。これにより少し記載を簡略化できます。
ただしここでは大きな記載削減効果はないので、参考として覚えておけば十分です。
プログラム8｜エクセルブックを上書き保存
|
1 |
ThisWorkbook.Save |
エクセルを上書き保存します。
プログラム9｜プログラム終了
|
1 |
End Sub |
プログラム1と対になるプログラムで、プログラム終了させるための記述です。
「End Sub」を読み込むと、プログラムが終了します。
プログラムの解説はここまでです。
このプログラムはスマートではない
ここまでプログラムについて紹介してきました。
しかし気づいた人も多いかもしれませんが、似たような記述が多く、メンテナンスが面倒です。
そこで以下のように配列を使って、書き換えを行うこともできます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
'プログラム0｜変数宣言の指定 Option Explicit 'プログラム1｜プログラム開始 Sub DataToOtherSheet() 'プログラム2｜シート設定 Dim ws1 As Worksheet, ws2 As Worksheet Set ws1 = ThisWorkbook.Worksheets("Sheet1") Set ws2 = ThisWorkbook.Worksheets("Sheet2") 'プログラム3｜ws1のC2～C7の値を取得 Dim myrange As Variant myrange = ws1.Range("C2:C7").Value '不要：プログラム4｜未入力のセルがあればflag=Trueとする 'プログラム5｜未入力セルがあればプログラムを終了させる Dim i As Long For i = LBound(myrange) To UBound(myrange) If myrange(i, 1) = "" Then MsgBox "未入力セルがあるので、入力すること" Exit Sub End If Next 'プログラム6｜ws2の最終行を取得 Dim cmax As Long cmax = ws2.Range("A65536").End(xlUp).Row 'プログラム7｜ws2の最終行の1行下にデータを蓄積 Dim k As Long With ws2 .Range("A" & cmax + 1).NumberFormatLocal = "@" .Range("A" & cmax + 1).Value = Format(cmax, "000") For k = LBound(myrange) To UBound(myrange) .Range("B1").Offset(cmax, k - 1) = myrange(k, 1) Next .Range("H" & cmax + 1).Value = Date End With 'プログラム8｜エクセルブックを上書き保存 ThisWorkbook.Save 'プログラム9｜プログラム終了 End Sub |
多少すっきりしていますが、これでも少し見づらい点はあります。
さらなる改善点を思いつく人もいるかと思います。
このように配列を使うと、記述が少なくすることができます。
しかし配列という概念がマクロ初心者には少しハードルが高いのと、配列がなくてもプログラムを記述することは上述のとおり可能です。
したがって、ここでは配列について紹介することはせず、記述は多いが分かりやすいプログラムを紹介することにしています。
なお配列については、以下のページで事例を交えて紹介していますので、興味がある人はご覧ください。
Excel VBAについて詳しく理解したいなら
VBAを活用すると、仕事を効率化できる幅を広げることができます。
たとえば私が実際にVBAを活用して効率化してきた作業は以下の記事で紹介しています。
興味がある人は以下の記事もご覧ください。
動画でも解説しています。
エクセルマクロVBAで出来ることを15の事例で紹介｜日常業務をラクにするヒントを見つけよう！
(音声が小さいので、ボリュームを上げてご覧いただければと思います)
VBAの勉強方法
私はプログラミング初心者からVBAを勉強を始めて少しずつレベルアップしていきました。
成長の過程は以下で紹介しています。
学習の過程では、意識すべきポイントがあります。
特に独学の場合だと、勉強を優先してしまい、肝心な実践を疎かにしがちです。
私の経験では、実践から逆算した勉強が必要だと考えています。
目指すべきは会社でお金をもらいながら勉強することです。
要はVBAを仕事の一つとして上司に認めてもらうのです。
そうすればわざわざ高いお金を払って勉強をする必要がなくなります。
しかも作業を自動化して、会社やチームに貢献しつつ、自らのスキルアップできます。
そのために必要な考え方を以下で紹介しています。
とはいえ、プログラミング初心者でVBAについて知識ゼロの人もいるはずです。
いきなり会社でVBAで使うことさえ、とてつもなくハードルが高く見えてしまうものです。
その場合は、VBAの基本について学ぶ必要があります。
たとえば車の運転も慣れてしまえば、たいしたことではありません。
しかし教習所で運転の基本を学び、免許を取得することで、公道で運転できるようになります。
VBAも同じです。VBAに免許はありませんが、まずは基本を学ばないことには会社で使えるレベルにはなりません。
実際に私もプログラミング初心者のときは、動画を見たり書籍を読んだりして勉強しました。
今はオンラインの教材で無料で学習できるものも多いです。
上記のリンクでは、私の経験から勉強にオススメの教材を紹介しています。
興味がある人はご覧ください。
VBAを自分で書けるようになる
さて、本記事で紹介したマクロを利用すれば、作業の自動化が可能になります。
しかしデメリットもあります。それはカスタムできないことです。
なぜなら、色々な要望が増えるからです。
この動画を見たとき、「もっと○○ができるのでは？」や「ここはなんとかならないのか」と感じる人は少なくないはずです。
例えば、「他の条件を付け加えたい」や「日付毎に条件を変えたい」といった要望が出るかもしれません。
このような要望を満たすには、マクロを勉強して自力でマクロを編集できるようになる必要があります。
もし、自力でマクロを編集できるようになれば、今より仕事の効率はグッと上がります。
実際、私も自力でマクロを書けるようになってからは、仕事の生産性が一気に上がりました。
他の人が30分～1時間かけて行う仕事が、ボタン一つで終わらせることができるようになったのです。
その結果、周囲からの信頼も増し、仕事で高い評価を得られるようになりました。
ただ、要望に応えるようになるためには、マクロを学ぶ必要があります。
まずは無料でマクロを勉強してみる
ウェブや書籍で勉強すれば、マクロを習得できると考えている人は少なくありません。
しかし、仕事で使えるマクロを習得したいなら、仕事で使える部分に特化した教材で学ぶことをお勧めします。
なぜなら、ウェブや書籍には仕事に関係しない部分まで提供していることが多いからです。
例えば、マクロ初心者なのに配列を学ぼうとする人がいます。実は配列なしでも仕事で使えるマクロを書くことは可能です。
しかし、マクロ初心者ほど「全ての知識が必要だ」と考えて、無駄な学習に時間を使ってしまうのです。詳しくは、こちらの記事で紹介しています。
そこで、私がお勧めするのは仕事に直結するマクロ教材です。とくにお勧めするのは、こちらの無料オンライン動画です。
なぜなら、仕事に直結する部分に絞って、エクセルマクロを学ぶことができるからです。
マクロの作り方・考え方から解説しているので、教材をしっかり学べばここで紹介したマクロをゼロから書けるようになります。
マクロ初心者が、仕事に直結したいマクロを学ぶなら、まずはこちらの無料オンライン動画を試すのがいいです。
興味がある人は、まずは無料でエクセルマクロの勉強を始めてみてください。
もっと学びたいと感じたら、さらに深く勉強をしてみることをお勧めします。