エクセルVBAを使って伝票作成の効率をアップさせる方法【サンプルVBA付】
エクセルVABを使って伝票作成を自動化したい！
こんな要望にお答えします。
本記事では、転記VBAやページの設定など、効率よく伝票を管理するマクロの作り方を紹介します。
作成した転記VBAを使った伝票作成エクセルがどのようなものか下記の動画で紹介しています。
この記事の最後では、伝票作成VBAの比較として伝票作成専用ソフト【Misoca】の紹介をしています。エクセルを使った伝票作成と専用ソフトを使った伝票作成で迷っている方は合わせてチェックしてみてください。
ページ設定
VBA作成の前にページの設定をやっておくことが重要です。
様式のページ設定は今後変更が発生しないよう作り込みましょう。
一覧表シート
伝票シート
設定シート
ページ設定のポイント
- セルの結合を確認
- 余白を確認
- 様式に間違いがないか確認
伝票の様式ができたら印刷プレビューで問題がないか確認してください。
もしVBAを作ったあとに様式の間違いがあれば、VBAを1から作り直さなければならないことがあります。様式のページ設定は慎重に行いましょう。
転記VBAの作り方
会社名と作成日で伝票を作成する転記VBA
サンプル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,jで取得"’ |
| 8 | Dim i As Date |
| 9 | i = Bsh.Cells(6, 36) |
| 10 | j = Bsh.Cells(4, 36) |
| 11 | "’伝票の値をリセットする"’ |
| 12 | gyoa = Ash.Cells(Rows.Count, 1).End(xlUp).Row |
| 13 | gyob = Bsh.Cells(Rows.Count, 2).End(xlUp).Row |
| 14 | Bsh.Range(Cells(6, 1), Cells(6, 10)).ClearContents |
| 15 | Bsh.Range(Cells(10, 2), Cells(gyob + 1, 10)).ClearContents |
| 16 | Bsh.Range(Cells(10, 11), Cells(gyob + 1, 15)).ClearContents |
| 17 | Bsh.Range(Cells(10, 16), Cells(gyob + 1, 19)).ClearContents |
| 18 | Bsh.Range(Cells(10, 20), Cells(gyob + 1, 23)).ClearContents |
| 19 | Bsh.Range(Cells(10, 24), Cells(gyob + 1, 27)).ClearContents |
| 20 | Bsh.Range(Cells(10, 28), Cells(gyob + 1, 33)).ClearContents |
| 21 | Bsh.Range(Cells(26, 22), Cells(26, 24)).ClearContents |
| 22 | Bsh.Range(Cells(26, 14), Cells(26, 19)).ClearContents |
| 23 | Bsh.Range(Cells(26, 28), Cells(26, 33)).ClearContents |
| 24 | "’一覧表から伝票へ値を転記する"’ |
| 25 | For k = 3 To gyoa |
| 26 | gyob = Bsh.Cells(Rows.Count, 2).End(xlUp).Row |
| 27 | If Ash.Cells(k, 1) = i And Ash.Cells(k, 2) = j Then |
| 28 | Bsh.Cells(6, 1) = j |
| 29 | Bsh.Cells(gyob + 1, 2) = Ash.Cells(k, 3) |
| 30 | Bsh.Cells(gyob + 1, 11) = Ash.Cells(k, 4) |
| 31 | Bsh.Cells(gyob + 1, 16) = Ash.Cells(k, 5) |
| 32 | Bsh.Cells(gyob + 1, 20) = Ash.Cells(k, 6) |
| 33 | Bsh.Cells(gyob + 1, 24) = Ash.Cells(k, 7) |
| 34 | Bsh.Cells(gyob + 1, 28) = Ash.Cells(k, 8) |
| 35 | Else |
| 36 | End If |
| 37 | Next |
| 38 | Bsh.Cells(26, 22) = Ash.Cells(3, 9) |
| 39 | Bsh.Cells(26, 14) = Application.WorksheetFunction.Sum(Range(Cells(10, 24), Cells(gyob, 24))) |
| 40 | Bsh.Cells(26, 28) = Bsh.Cells(26, 14) * (Bsh.Cells(26, 22) / 100 + 1) |
| 41 | End Sub |
実行結果
会社名だけで伝票を作成する転記VBA
サンプルVBA
| 1～8 | 会社名と作成日で伝票を作成する転記VBAと同じ |
| 9 | 削除 |
| 10～26 | 会社名と作成日で伝票を作成する転記VBAと同じ |
| 27 | If Ash.Cells(k, 2) = j Then |
| 28～41 | 会社名と作成日で伝票を作成する転記VBAと同じ |
会社名と作成日で伝票を作成する転記VBAとほぼ同じですが、9行目と27行目を修正するだけで会社名だけで伝票を作成するVBAにすることができます。
9行目と27行目は関数IFに関係している箇所です。
関連記事「For文と関数IFの組み合わせ方法」では、条件分岐と繰り返し処理を組み合わせた効率化マクロの作り方が紹介されています。
転記VBAの実行方法
ユーザーフォームによる実行
私の職場ではユーザーフォームによる実行が最も使われています。伝票シート内の情報はは全て一覧表シートから転記されるので編集する必要がありません。そのためユーザーフォームでテキストボックスを設ければこの部分だけ編集すればいい！と一目でわかるので今回のような転記させるVBAにユーザーフォームによる実行はとても適しています。
サンプルVBA
| 1～8 | 会社名と作成日で伝票を作成する転記VBAと同じ |
| 9 | i = UserForm1.TextBox1.Value |
| 10 | j = UserForm1.ListBox1.Value |
| 11～26 | 会社名と作成日で伝票を作成する転記VBAと同じ |
| 27 | If Ash.Cells(k, 1) = i And Ash.Cells(k, 2) = j Then |
| 28～41 | 会社名と作成日で伝票を作成する転記VBAと同じ |
ユーザーフォームからテキストボックスとリストボックスの値を取得し、取得した値を変数として使用しています。
リストボックスでは設定シートから会社名を取得し表示する！というVBAにしています。
リストボックスを一覧表から取得するサンプルVBA
| 1 | Private Sub UserForm_Initialize() |
| 2 | Dim Ash As Worksheet |
| 3 | Dim Bsh As Worksheet |
| 4 | Dim Csh As Worksheet |
| 5 | Set Ash = Sheets(“一覧表") |
| 6 | Set Bsh = Sheets(“伝票") |
| 7 | Set Csh = Sheets(“設定") |
| 8 | Dim re As Long |
| 9 | Dim Li As String |
| 10 | gyo = Csh.Cells(Rows.Count, 1).End(xlUp).Row |
| 11 | For i = 2 To gyo |
| 12 | Li = Csh.Cells(i, 1).Value |
| 13 | ListBox1.AddItem Li |
| 14 | Next |
| 15 | End Sub |
実行結果
セルのクリックによる実行
ユーザーフォームが邪魔くさい！という場合におすすめがセルのクリックによるVBAの実行です。
サンプルVBA
| 1 | Private Sub WorkSheet_SelectionChange(ByVal Target As Range) |
| 2 | Dim Ash As Worksheet |
| 3 | Dim Bsh As Worksheet |
| 4 | Dim Csh As Worksheet |
| 5 | Set Ash = ThisWorkbook.Worksheets(“一覧表") |
| 6 | Set Bsh = ThisWorkbook.Worksheets(“伝票") |
| 7 | Set Csh = ThisWorkbook.Worksheets(“設定") |
| 8 | "’ドロップダウンリストのリセット"’ |
| 9 | ActiveSheet.Range(“AJ4").Validation.Delete |
| 10 | ActiveSheet.Range(“AJ11").Validation.Delete |
| 11 | On Error Resume Next |
| 12 | gyoc = Csh.Cells(Rows.Count, 1).End(xlUp).Row |
| 13 | For i = 2 To gyoc |
| 14 | If (Target.Column = 36) And (Target.Row = 4) Then |
| 15 | risto = WorksheetFunction.Transpose(Csh.Range(Csh.Cells(2, 1), Csh.Cells(gyoc, 1))) |
| 16 | With Target.Validation |
| 17 | .Add Type:=xlValidateList, Formula1:=Join(risto, “,") |
| 18 | .ShowError = False |
| 19 | End With |
| 20 | ElseIf (Target.Column = 36) And (Target.Row = 11) Then |
| 21 | risto = WorksheetFunction.Transpose(Csh.Range(Csh.Cells(2, 1), Csh.Cells(gyoc, 1))) |
| 22 | With Target.Validation |
| 23 | .Add Type:=xlValidateList, Formula1:=Join(risto, “,") |
| 24 | .ShowError = False |
| 25 | End With |
| 26 | End If |
| 27 | Next |
| 28 | If Err <> 0 Then |
| 29 | Err.Clear |
| 30 | End If |
| 31 | If (Target.Column = 36) And (Target.Row = 7) Then |
| 32 | Call 伝票転記 |
| 33 | ElseIf (Target.Column = 36) And (Target.Row = 12) Then |
| 34 | Call 伝票転記2 |
| 35 | End If |
| 36 | End Sub |
実行結果
Targetを使ってVBAの実行を行います。
サンプルVBAでTargetを使用している箇所
- 14～19行目で｢36列目、4行目のセルをクリックしたときプルダウンリストを表示する｣
- 20～25行目で｢36列目、11行目のセルをクリックしたときプルダウンリストを表示する｣
- 31～32行目で｢36列目、7行目のセルをクリックしたとき転記VBAを実行する｣
- 33～34行目で｢36列目、12行目のセルをクリックしたとき転記VBAを実行する｣
Targetはとても便利な機能でプルダウンリストを表示するVBAによく使われます。
会社名の入力にはプルダウンリストから選択する！という方法が便利です。今回の転記VBAでは関数IFを使って会社名で検索をする！という方法です。そのため会社名を誤入力してしまったらVBAが動作しません。
プルダウンリストから会社名を選択する方法では誤入力によるVBAが動作しない！という心配がありません。
実行結果
設定シートの会社名からプルダウンリスト作成しています。リストの内容が変化するとプルダウンリストの内容も自動的に変化するので、大勢の人が扱うエクセルファイルにおすすめです。
大勢の人が扱うエクセルファイルでは、誰かが設定シートの会社名に新しく契約した会社名をいつの間にか追加してプルダウンリストが全て表示されていない！という事象がときどき発生していました。
下記の記事ではプルダウンリストの内容を一覧表から取得する方法について詳しく紹介しています。
このプルダウンリストの内容が変化するVBAを導入すれば、プルダウンリストに関するトラブルが激減します。
VBA初心者が効率化マクロを導入する方法
VBAなんて扱ったことないけど、伝票作成を自動化するマクロを導入したい！
こんな問題を解決する方法がこちら
- マクロの開発を外注に依頼する
- マクロが設定されたエクセルファイルをダウンロードする
- 参考書やネットでVBAスキルを身に付ける
VBAスキルを身に着ける前は、「マクロ開発の外注」で対応していましたが、
今では自分で効率化マクロをサクッと導入しちゃいます。
完成されたマクロを使ってエクセル業務を処理する
↓
作業効率が２倍以上アップ！！
↓
便利すぎて自分でもマクロを設定したいと考え始める
↓
参考書やネットの情報でVBAスキルを身に付ける
↓
マクロ開発を代行できる程のスキルを習得
VBAは
他のプログラミングに比べて参考書やネットで紹介されている情報量が多いので初心者でも導入しやすい
という特徴があります。
外注した方が楽だけど、費用がかかる。自分で導入すれば費用は０円だけど時間がかかる。。
いずれにせよ、効率化マクロを導入できれば作業効率が飛躍的にアップするのは間違いありません。
あなたに合った方法で導入しちゃいましょう。
マクロ開発を外注に依頼する
マクロ開発を外注に依頼する方法は
VBAを全く扱えない方でもエクセル作業を自動化できる最も簡単な方法
です。
「こんなデータ転記を自動化したい」「作った伝票を自動的にPDFで保存したい」「決まった様式に写真を自動で貼り付けたい」
こんな感じで実現したい内容を依頼するだけで業務内容に合ったマクロを開発してくれます。
ガッツポーズの人
デメリットは、費用がかかることだけ。
開発内容、依頼先によってかかる費用は大きく異なります。
私が会社員の頃利用していた業者さんは１マクロあたり2万円～3万円の費用がかかりました。
疑問がある人
ちなみに、マメBlogでもエクセルマクロ開発代行サービスを承っています。
開発内容の確認、VBAコードの設定、動作確認後の調整、など全ての工程を私（マメ父ちゃん）が行っているので、
費用が安い！スピード納期！！
でやらせてもらっています。
ほとんど独学で身に付けたVBAスキルなので、内容によっては開発できないこともあるのでご了承ください。
ご相談、見積もり依頼は完全無料ですので気になる方は下記のリンクよりお問合せ下さい。
マクロが設定されたエクセルファイルをダウンロードする
VBA初心者でも『マクロが設定されたエクセルファイルをダウンロード』することで、
すぐに使っているエクセルにマクロを導入することができます。
VBA初心者でも形になっているマクロをゲットできる、費用が安い、
というメリットがあります。
しかし、
想定するマクロを見つけることが難しい、業務内容に合わせるためのアレンジが必要
というデメリットもあります。
ガッツポーズの人
個人ブログやファイルを販売できるサイト「note」や「Tips」ではいろんなマクロの情報が掲載されているので是非参考にしてみてください。
今までマメBlogで受注した効率化マクロを下記のnoteで掲載していますので併せてチェックしてみてください。
参考書やネットでVBAスキルを身に付ける
先程も紹介しましたが、
VBAは他のプログラミングに比べて参考書やネットで紹介されている情報量が多いので初心者でも導入しやすい
プログラミングです。
言い換えると、
情報量が豊富なので自分で勉強できちゃう
ということです。
私のVBAスキルはほとんど独学で身に付けたもので、
考えた処理のほとんどをVBAで表現できるレベルにまで到達した！
と思っています。
私の作ったマクロをプロのプログラマーが見れば、
お前の書くコードはインチキだ！「変数の宣言」とか「引数」を正しく使えよ！！
と感じるはずです。
でも、
私からすればどうでもいい！！！想定した内容を正しく処理できればよくないですか！？
独学であっても『写真を貼り付けるマクロ』『全てのシートをPDF出力するマクロ』『連番を振るマクロ』など、
業務内容に合ったマクロを開発できるようになれます。
独学でVBAスキルを身に付けるコツは、
エラーが発生してもいいからひたすらコードを書く、なんでもいいからエクセル作業をマクロ化してみる
です。
とは言っても、全くの知識0の方は何をすればいいかわからないですよね。
VBA知識0の初心者の方は、参考書『たった1秒で仕事が片づくExcel自動化の教科書【増強完全版】』を読んでみてください。
VBAの知識0の方でもすいすい読み進められる内容になっています。
やさしい内容にも関わらず「え！！VBAを使えばこんなことできるの！？」と感動すると思います。
実際に私はこの参考書を読んでからVBAの勉強を始めました。
ひらめく人
VBAの魅力を発見した後は、
ひたすら自動化したい内容のコードを書くだけ
です。
【VBA 伝票作成】【VBA テキスト転記】でネット検索すれば知りたいコードをすぐにゲットできます。
実現したい処理に向かって、「調べる→書いてみる→エラーを改善する→調べる」を繰り返していると
自然といろんなマクロが作れるようになっているはずです。
VBAに興味が湧いてきたという方は今からVBA学習をスタートさせちゃいましょう。