先回から販売の帳票（見積書、納品書、請求書）を自動で作成するエクセルソフトを作っています。
今回は第2回目、必要なデータをテンプレートに移し、計算し、帳票を仕上げていきます。
こんにちは、じゅんぱ店長（@junpa33）です。
帳票のデータの移動と計算は、コピペや集計範囲設定など、マウスクリックでカチカチと手先の忙しい作業です。
けれどもVBAコードを使えば、簡潔に数行のVBAコードの記述でＯＫという話をします。
テンプレートに仕上げた帳票にデータを移動する作業をして行きます。
タダでさえ間違ってはいけない作業ですので、出来るだけ手作業をせずに済ませたいところです。
第1回目の記事を参考にするには、こちらです。
顧客に出す伝票のテンプレートをエクセルVBAコードだけで作る
顧客に出す伝票のテンプレートをエクセルVBAコードだけで作る見積納品請求3点伝票作成ソフト作成一覧
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
コンテンツ
エクセルで頻繁に行うデータ移動
エクセルを使っている中で、普段より最も頻繁に行っている作業がセルデータの移動です。
- 同じ数字を1段下のセルでも使いたい。
- 同じ文章を印刷2ページ目でも使いたい。
- ほかのシートの内容を丸々こちらに移したい。
などなど挙げれば切りがないほどです。
セルデータ移動のテクニック向上は、
「エクセルを使いだした人」、「使い慣れてきた人」、「他の人にレクチャーできるような人」
それぞれ皆さんの利用レベルの違いも有りますが、「こう出来たらうれしい」という
悩み的なもどかしさを、共通して持たれている部分でもあると思います。
悩める君
そんな中で、結構おられるのが、ゴリゴリに「何が何でもエクセル関数」、関数一本やりの方です。
エクセル関数は非常に便利ですが、移すデータの数量、移動量によっては、
移動先のシートのセルは「＝関数式」の大名行列になっているのではないでしょうか。
間違って消してしまったらどうしようと、セルにカーソルを乗せることすら怖くなる場合もありますよね。
データの移動は、コピペとLookupメインですか
データの移動についてはコピーペースト、データ取出しはLookupが合言葉のようになっている方もいます。
皆さんは、少なからず何回かは、「あっ！ミスッた」と思うことがあるでしょう。
カーソルを十字にして上から下へと「スルスル」と動かしたとき、関数の数式のセル位置指定がおかしくなっていたとか。
で、この「ありゃー」を上手く解決するには、
最後はやはりコードをモジュールに打ち込んで、VBA化するしかありません。（自分もそれに行き着きました）
実は、VBAを使うと、このようなことも実に簡単に解決してしまいます。
「移し元」と「移し先」のBOOK名とシート名とセル位置同士を「＝」で繋ぐだけです。
しかもセル位置一つずつ行うのではなくて、移動の範囲指定など「位置を変数化」して記述するだけです。
今回作成している請求納品見積書の帳票などは表示のデータが何十行になる事もありますので、これは必須のスキルになります。
食わず嫌いにならずにこれからVBAにチャレンジしてみましょう。
VBAコードで簡潔にデータを移します
それでは、帳票にデータを移すVBAコードを組み立てていきます。
コード記述はModule2で行います。新たにモジュールを挿入してください。
帳票作成プロシージャー
VBAコードの全体を通してこのようになります。
入力セルの最終行を取得する方法はこちらを参考にしてください。
エクセルVBA データ入力済セルの最終行番号を取得する方法
Sub 帳票作成()
Dim a As Long, m As Long, N As Long
Dim SaRow As Long, KeRow As Long
Worksheets(“作業シート”).Select
SaRow = Cells(Rows.Count, 2).End(xlUp).Row
‘金額合計を計算
GS = WorksheetFunction.Sum(Range(Cells(2, 6), Cells(SaRow, 6)))
‘罫線を引く範囲
If SaRow < 27 Then
KeRow = 39
Else
KeRow = 39 + Application.WorksheetFunction.RoundUp((SaRow – 26) / 35, 0) * 35
End If
‘請求納品見積書シートへテンプレートの再構築
For a = 2 To 4
Worksheets(a).Select
Module1.テンプレ作成2
With Worksheets(a)
.Range(“A13:G” & KeRow).Borders.LineStyle = xlContinuous
.Range(“B14:B” & KeRow).Font.Size = 10
.Range(“B13:B” & KeRow).WrapText = True
.Range(“C13:D” & KeRow).HorizontalAlignment = xlCenter
.Range(“E13:F” & KeRow).NumberFormatLocal = “\ #,##0”
.Range(“F3”).Font.Size = 10
.Range(“F3”) = “令和 年 月 日”
End With
Next a
‘請求納品見積シートへデータ転記
For N = 2 To SaRow
For m = 1 To 7
For a = 2 To 4
Worksheets(a).Cells(N + 12, m) = Worksheets(“作業シート”).Cells(N, m)
Worksheets(a).Cells(SaRow + 13, 2) = “税込合計”
If SaRow + 13 <> 39 And ((SaRow + 13) – 39) Mod 35 <> 0 Then
Worksheets(a).Cells(SaRow + 14, 2) = “以下余白”
End If
Worksheets(a).Cells(SaRow + 13, 6) = GS
Worksheets(a).Cells(11, 2) = GS
Next a
Next m
Next N
‘別のプロシージャー呼び出し
Module2.宛名設定
End Sub
合計金額を計算
ワークシート関数をVBAで利用します。
合計関数SUMはこのようにコード化できます。
罫線を引く範囲
デフォルトのテンプレートは、１ページ分を準備していました。これの使用行数は39行です。
当然、使用場面ではこれが２ページ３ページ必要になりますので、行数を増やさなければいけません。
つまり、このコードによってデータ項目数に合わせて罫線を引く範囲の確定を行います。追加ページの１ページは35行になります。
請求納品見積書シートへテンプレートの再構築
請求書、納品書、見積書シートでテンプレートを再作成します。
同じVBAコードを３回繰り返して、それぞれのシートに作成するように記述しても良いのですが、
「請求書シート＝Worksheets(2)」・「納品書シート＝Worksheets(3)」・「見積書シート＝Worksheets(4)」ということを使って
その（２）と（３）と（４）を変数化して「For ～ Next」のループ処理を行っています。
請求納品見積書シートへデータ転記
VBAコードでのデータ移動の一つの方法（コード組み立て）です。
何十何百のデータ項目数があってもこれですべてＯＫです。
先ほどの「For ～ Next」のループ処理の方法を使って、そのWorksheetの方法と、
セル位置の指定方法「Cells(行番号、列番号)」についても変数化しています。
行番号・・・「作業シート」の2行目からデータの最終行SaRow
列番号・・・「作業シート」の1列目から7列目
ページの最終行が「税込合計」の場合は、「以下余白」を表示しないようにします。
ここではFor～Nextの入れ子構造にしています。
この入れ子の構造の考え方については、この記事が参考になります。
別のプロシージャー呼び出し
Module2で、別に記述している「宛名設定」というプロシージャーを呼び出して実行します。
宛名設定プロシージャー
インプットボックスについてはこちらの記事を参考にしてください。
エクセルVBA 2つのInputBox。特徴を生かした使い分けMatch関数について詳しくはこちらになります。
エクセルVBAで使うMatch関数 活用度アップでテッパン関数に！
Sub 宛名設定()
Dim Res As Variant, CosNo As Long
Dim Ybn, Jus, Ate
Res = 0
Workbooks(“見納請3点伝票作成.xlsm”).Activate
Worksheets(“宛名の登録”).Select
‘仮のコードです
CosNo = InputBox(Prompt:=”得意先コードを入力してください”)
If CosNo = Null Then Exit Sub
On Error GoTo 0
On Error Resume Next
Res = WorksheetFunction.Match(CosNo, Range(“B:B”), 0)
Ybn = Range(“D” & Res).Value ‘郵便番号
Jus = Range(“E” & Res).Value ‘住所
Ate = Range(“C” & Res).Value ‘宛名
For a = 2 To 4
With Worksheets(a)
.Range(“B3”) = Ybn
.Range(“B4”) = Jus
.Range(“B6”) = Ate & “ 様”
End With
Next a
Worksheets(“宛名の登録”).Select
If Res = 0 Then
MsgBox “一致する得意先コードはありません。”
Exit Sub
End If
End Sub
「宛名の登録」シートに入力した発行相手先のデータを調べて移してきます。
調べ方は、「顧客番号」を利用して、「宛名の登録」シートでリストマッチングでピックアップします。
リストにない場合は「ありませんでした。」メッセージを表示します。
また、この「顧客番号」ですでにお使いの販売管理ソフトと連携させることも可能です。
この「顧客番号」でのマッチングは、後の記事で紹介しますが、日々の販売取引を管理する販売管理ソフトと連携させるために必要な事項になります。
店長の会社では、「ソリマチ 販売王」の顧客IDとリンクさせています。
仮のコードです
今の時点では、インプットボックスに「顧客番号」を入力するようにしています。
将来的にユーザーフォームに入力欄を作成して、そこからコントロールするようになります。
VBAコードでデータの移動と計算。帳票作成のまとめ
ここまでで仕上がった帳票
 |  |
| １ページ目 | ２ページ目 |
データ移動と合計計算のVBAコードは結構簡単なコードだと理解いただけたでしょうか。
移動するデータが、何個何百個あってもこれで対応できるということです。
いろんな部分に応用が利くと思います。使ってみてください。
次回は、今回までの作成した帳票を印刷するところまでを紹介したいと思います。
次の記事を読むのはこちら↓になります。
印刷設定を行うエクセルVBAで、無用なプリントミスをなくす方法
印刷設定を行うエクセルVBAで、無用なプリントミスをなくす方法
エクセルVBAの独習を始めるためのポイントについて
エクセルVBAを独学で習得するためのポイントは？良書との出会いは重要 基礎知識習得のためのオンライン学習講座の参考記事 
エクセルVBA初級者がUdemyで動画学習する講座おすすめ５選と無料講座の上手な使い方。 今回の記事はここまでです。
最後までご覧いただき有難うございました。
エクセルVBAを使って業務効率を上げて行くのに、始めのうちに知っておくといい内容を纏めてみました。
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む
続きを読む