注文書テンプレートを出版社別に複製して発注書籍データ転記するVBAコード

datatenkieyecatch

FAX注文書の作成時間の短縮を目指して作ってきたエクセルVBAも大詰めです。この回で一応の完成となります。
恐らく注文書30通作るのに2時間かかっていた人なら同じ作業を15分で完成させることができると思います。

こんにちは、じゅんぱ店長(@junpa33)です。

 

今回はテンプレートを複製して出版社別の注文書のシートを作ります。

「FAX注文書テンプレート.xlsm」での作業が中心となります。

 

この記事でVBAコード組み立ては、ほぼ最終となります。

 

FAX注文書作成の関連の記事はこちらになります。

「FAX発注書作成」の記事一覧を開く

 

書籍データ転記する出版社別注文書のVBA組み立ての流れ

 

手順1

「FAX注文書テンプレート」を先回作りましたが、テンプレートはホントの”原紙”の機能としてのみ使います。

実際に使用する場面では、発注先分の枚数が必要になります。ですので、”原紙”として必要分をコピーして使います。

エクセル上で、「P1」「P2」「P3」・・・とページのシートを必要分増やします。

手順2

「発注資料表」に出力した注文内容をコピーした「テンプレート」の各ページごとに転記していきます。

手順3

さらに番線印欄に、「番線印」か「番線印情報」を挿入します。これは、「設定とスタート」シートで選択できます。「番線印」は画像データ(.Jpgなど)でいいと思います。(背景は白色)また、「発信元情報」も記入します。

「番線印」か「番線印情報」は「テンプレート」のコピー各ページに必要分挿入され、「発信元情報」は”原紙”である「テンプレート」に挿入されます。

手順4

この「設置とスタート」シートにVBAコード起動用のボタンを設置します。

手順5
注文書をプリントアウトします

今回はこの部分のVBAコード作成はありません。

発注情報がすべて入力された各ページは、手作業でプリントアウトしてください。VBAコードで自動化はしていません。

手順6

「オールクリアー」の操作ボタンで、情報が消去されます。「テンプレート」のコピー各ページと「発注資料表」の内容がクリアーされます。

ただし”原紙”である「テンプレート」に挿入された「発信元情報」は消去されません。消去するには「設定とスタート」シートの「発信元情報」欄の入力情報を別に消去します。

 

この様な流れになります。

 

複製して発注書籍データ転記するVBAコード組み立て

 

VBAコードを組み立てていきます。

VBAコードをプロシージャー名「メインデータ転記」とします。

Module1を挿入します

 

「FAX注文書テンプレート.xlsm」で、”開発タブ”から”Visual Basic”を起動してください。

モジュール(Module1)を挿入追加してください。

挿入追加の方法の確認は「VBE(ビジュアルベーシックエディター)を起動する」です。

関連記事

エクセルVBA 始めての起動。VBEの立ち上げ、保存と終了

 

発注資料表のページ数分のシート作成

作りたい枚数分テンプレートをコピーして、「P1」、「P2」「P3」・・・・という風にシートを追加作成していきます。

シートを追加する方法は2種類あります。

MEMO
「Add」メソッドは名前の通りシートを追加します。この場合はSheet4,Sheet5と増えていきます。シート中のセルの書式やページの設定はBOOKのデフォルト状態です。
シートの内容を他のシートからコピーして貼り付ける場合は「Add」メソッドを使った場合はセルの書式設定は引き継がれますが、ページ設定は引き継がれません。

 

「Copy」メソッドを使った場合は、コピー元のシート中のセルの書式やページの設定がすべて引き継がれます。
特に用紙サイズや印刷余白それと印刷範囲設定もコピーされますので、印刷出力する場合には特に便利です。

関連記事

ワークシートのコピーを最速に理解!VBAコードで選ぶコピー結果

 

MEMO

入力セルの最終行を取得する方法はこちらを参考にしてください。

データ入力済セルの最終行番号を取得するVBAコード

If条件文についてはこの記事を参考にしてください。

「If条件文」のVBAコードの組み方。条件の絞り方を最速に理解。

メッセージボックスについてはこの記事を参考にできます。

メッセージボックス MsgBox実際の使い方を最速に理解

For~Nextの使い方については、この記事も参考にしてください。

エクセルVBA!For~Nextの入れ子ループを最速に理解する

 

コードはこのようになります。

コード①

Sub メインデータ転記()
Dim Nrow As Long
Dim e As Long, ee As Long, x As Long
Dim Sn As String
Worksheets("発注資料表").Select
Nrow = Cells(Rows.Count, 13).End(xlUp).Row
       e = Nrow Mod 9
       ee = Nrow \ 9
    If ee = 0 Then
        If e < 3 Then
           MsgBox "作成するFAX注文書はありません。", vbOKOnly + vbInformation, "注文書作成"
           Worksheets("設定とスタート").Select
           Exit Sub
        Else
           x = 1
        End If
    Else
        x = ee + 1
    End If
    For s = x To 1 Step -1
            Sn = "P" & s
            Worksheets("テンプレート").Select
            Worksheets("テンプレート").Copy after:=Worksheets("テンプレート")
            ActiveSheet.Name = Sn
    Next s

 

datatenki001-1a

 

ページごとに注文データを転記します

 

転記項目が多くなっていますので、変数の表示文字の間違いに注意します。

「横書き」から「縦書き」への転記がありますので、後でまとめてページ設定とセルの書式設定を行います。

 

変数を宣言します。数が多いので少し長くなります。

コード②の1

Dim PR As Long
Dim BV As Variant, SUV As String, SOV As String, TYV As String
Dim SAV As Variant, HOV As Variant, SIV As Variant, HAV As Variant
Dim SJV As String, ISV As String, KYV As Variant, TDV As String

 

コードはこのようになります。

 

MEMO

For~Nextの使い方については、この記事も参考にしてください。

エクセルVBA!For~Nextの入れ子ループを最速に理解する

Match関数について詳しくはこちらになります。

エクセルVBAで使うMatch関数 活用度アップでテッパン関数に!

 

コード②

    For s = 1 To x
            Sn = "P" & s
                For t = 1 To 5
                    Worksheets("発注資料表").Select
                    PR = WorksheetFunction.Match(Sn, Worksheets("発注資料表").Columns("A"), 0) + 1
                    BV = Range("A" & PR + t)        '分類名
                    SUV = Range("B" & PR + t)       '出版社名
                    SOV = Range("C" & PR + t)       '書名
                    TYV = Range("D" & PR + t)       '著者名
                    SAV = Range("E" & PR + t)       '冊数
                    HOV = Range("F" & PR + t)       '本体価格
                    SIV = Range("G" & PR + t)       'シリーズ名
                    HAV = Range("H" & PR + t)       '版数
                    SJV = Range("I" & PR + t)       '送品条件
                    ISV = Range("J" & PR + t)       'ISBN
                    KYV = Range("K" & PR + t)       '客注名
                If SOV = "" Then
                    TDV = ""
                Else
                    TDV = Date
                End If                              '発注日
                    Worksheets(Sn).Select
                    tt = (t - 1) * 4
                    Cells(3, 22 - tt) = SAV
                    Cells(4, 21 - tt) = "ISBN " & ISV
                    Cells(5, 21 - tt) = SJV
                    Cells(7, 24 - tt) = SUV
                    Cells(7, 22 - tt) = SIV & vbCrLf & SOV
                    Cells(9, 24 - tt) = TYV
                    Cells(10, 22 - tt) = HAV & "版"
                    Cells(11, 22 - tt) = HOV
                    Cells(12, 22 - tt) = TDV
                    Cells(13, 22 - tt) = KYV
                    Cells(15, 21 - tt) = BV
                If SUV <> "" Then
                    Range("AD2") = SUV & "  様"
                End If
                Next t
    Next s

 

datatenki002-2a

 

 

FAX発信の宛名については「出版社名」のデータをそのまま使っています。

適宜修正していただく必要があるかもしれません。

 

番線印を押します 発信元を表示します

 

「Sheet1」のシートで”注文元の基本設定”とVBAマクロのコントロールボタンを設置します。

 

電子印の作り方については、この記事が参考になります。

 

「Sheet1」をリネームします

 

「Sheet1」をリネームして「設定とスタート」とします。

 

”発信元情報”という記入表と”番線印、番線印情報”という入力表を作ります。

この様な感じになります。デコレートはお好みでよろしいかと思います。

datatenki003a

 

発信元情報表について

 

この発信元情報を「テンプレート」に転記します。

コードはこのようになります。コード②の下に入れてください。

コード③

Sub 発信元セット()
   Dim HJa As Variant, HJb As Variant, HJc As Variant, HJd As Variant, HJe As Variant
   Worksheets("設定とスタート").Select
	HJa = Range("I3")
	HJb = Range("I4")
	HJc = Range("I5")
	HJd = Range("I6")
	HJe = Range("I7")

   Worksheets("テンプレート").Select
	Range("AD10") = HJa
	Range("AC10") = HJb
	Range("AB10") = HJc
	Range("AA10") = HJd
	Range("Z10") = HJe
End Sub

 

datatenki004a

 

番線印、番線印情報について

 

作表の時に「チェックボックス」を2つ挿入します。

それぞれのチェックボックスのラベルを「番線印」と「番線印情報」とします。

チェックボックスの挿入方法はコマンドボタンの挿入方法と同じです。

こちらで確認できます。「データ抽出ボタンを設置する

 

MEMO

チェックボックスの設置方法はこちらの記事を参考にできます。

【2種類のチェックボックス】エクセルシートへの設置と使い方の違いを解説

 

コードはこの様になります。コード①の間に入れます。

 

コード④

    If Worksheets("設定とスタート").CheckBoxes(1).Value = xlOn Then
        BNa = 1
    End If
    If Worksheets("設定とスタート").CheckBoxes(2).Value = xlOn Then
        BNb = 1
    End If
    If BNa = 1 And BNb = 1 Then BNa = 0

 

datatenki005aa

 

チェックボックスがチェックされているかどうかを調べます。

2つともチェックしている場合は「番線印情報」が優先されます。

2つともチェックしていない場合はデータ入力されません。

 

次に、選択された番線情報を記入された注文短冊に挿入します。

コード⑤はコード②の間に挿入します。

コード⑤

If BNa = 1 Then
  If SAV > 0 Then
      Worksheets("設定とスタート").Range("H13").Copy _
          Worksheets(Sn).Range(Cells(2, 21 - tt), Cells(2, 24 - tt))
  End If
ElseIf BNb = 1 Then
    If SAV > 0 Then
        Worksheets("設定とスタート").Range("I13").Copy _
           Worksheets(Sn).Range(Cells(2, 21 - tt), Cells(2, 24 - tt))
    End If
Else
End If

 

datatenki006a

 

操作のためのボタンを設置します

 

「設定とスタート」シートに操作ボタンを設置します。

 

MEMO

コマンドボタンの設置方法はこちらの記事も参考になります。

エクセルVBA コマンドボタンをシートに設置する2つの方法

シートのクリアのためのエクセルVBAコードの作り方については、この記事が参考になります。

エクセルVBA シートのクリアーを目的のメソッド別に最速理解

 

「発信元セット」を起動するボタンと「メインデータ転記」を起動するボタンを設置します。

「発信元情報記入ボタン」は「発信元セット」、「発注書作成ボタン」は「メインデータ転記」です。

ボタンラベルの名前付けとデザインは適宜お好みでよろしいかと思います。

datatenki007a

 

利用済のデータをクリアします

 

「オールクリアー」の操作のためのコードとボタンも作成します。

クリアーの内容は「各ページ」シートと「発注資料表」の内容全クリアーです。

コードはこのようになります。

 

コード⑥

Sub オールクリアー()
Worksheets("発注資料表").Select
Nrow = Cells(Rows.Count, 13).End(xlUp).Row
       e = Nrow Mod 9
       ee = Nrow \ 9
    If ee = 0 Then
        If e < 3 Then
      Worksheets("設定とスタート").Select
          Range("A1").Select
          Exit Sub
        Else
           x = 1
        End If
    Else
        x = ee + 1
    End If
Application.DisplayAlerts = False
If Worksheets.Count <= 3 Then
    Worksheets("発注資料表").Cells.Clear
    With Worksheets("発注資料表")
        .standard.Height
        .StandardWidth
    End With
    'ActiveSheets.Clear
    Application.DisplayAlerts = True
Else
    For s = 1 To x
            Sn = "P" & s
            Worksheets(Sn).Delete
    Next s
    Application.DisplayAlerts = True

    Worksheets("発注資料表").Cells.Clear
    With Worksheets("発注資料表")
        .Cells.UseStandardHeight = True
        .Cells.UseStandardWidth = True
    End With
End If
Worksheets("設定とスタート").Activate
Range("A1").Select
End Sub

 

操作ボタンを設置した全体はこのような感じになります。

datatenki008a

 

発注書籍データ転記するVBAコードのまとめ

 

これで一応の作業は完了です。あとは発注書の表示を書式設定で適切化することになります。

お疲れさまでした。

 

けれど、自分的にはもう1ステップ加えたい機能があります。

普段の実務でFAX発注書を送信されている方ならお分かりかもしれません。

FAX電話番号を調べて発信する作業です。FAX機のワンタッチボタンに記憶させている番号ばかりであればいいのですが、番号調べは結構面倒です。

そこで、次の記事では、

最終回です。最後の追加機能として、作成した発注書に番号が自動に表示されるようにしたいと思います。あと使い方の全体の流れを最後に解説したいと思います。

 

短期間でエクセルVBAの独学習得を目指したいなら

 

エクセルVBAを独学する独習方法は、学習者それぞれ十人十色、多種多様と思われます。

けれども、

出来るだけ効率よく学習するためには、いくつかの大切なポイントがあります。

独学でもVBA習得の中級クラスに達するのはそんなに難しいことではありません。

先人が行った勉強方法をあなたがそのまま利用すればよいということです。

vbastudyeyecatch002 エクセルVBAを独学で習得する!ために大切な7つのポイントを解説します

独習のための大切な7つのポイントは、上記記事にて解説しています。

重要ワード

独習によるVBA習得のキーワードは、

出来るだけ多くの実例に触れること!

です。

正直、VBAの学習について自分の周りの仕事(業務)からだけ実例を得るのでは効率良い習熟は無理です。

ハッキリ言って、

本当に短い期間でVBA習得を成功させたいなら、今使っている参考書が良書かどうかを判断し、新ツールとしてオンライン学習も取り入れて行うことが、

手っ取り早く短期間習得できるというのは間違いないでしょう。

 

 

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。
エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

 

エクセルVBAの独習には動画学習という方法もあります。 目と耳両方を使って学習することでさらに勉強効率を上げることもできると思います。
エクセルVBA初級者がUdemyで動画学習する講座おすすめ5選と無料講座の上手な使い方。

 

大村式【動画&テキスト】Excelマクロ&VBA最高のはじめ方
初めてのVBAの勉強、構えなくても気軽に始められる。
vbastudy020a
vbastudy021a

電子書籍版「大村式【動画&テキスト】Excelマクロ&VBA最高のはじめ方」をamazonで見てみる

(著者)大村あつし
(出版社)技術評論社
(税込価格)1,628円(本体1,480円+税)

学習書の新しい形です。
YouTubeと完全リンクした参考書です。入門と基礎を重点的に22本の動画で解説をしています。
ちょっとした空き時間を利用してでもスマホがあれば学習ができます。
動画は優しい語り口調で、視聴者にある意味安心感を与えてくれます。動画は5分から20分間ぐらいで22本の構成です。
文章解説と動画解説の関係性は、動画解説が主で、文章解説がサポートいう使い方もできます。

 

エクセルVBAを使って業務効率を上げて行くのに、始めのうちに知っておきたい内容を纏めています。

「VBA最速理解」の記事一覧を開く

 

今回の記事はここまでです。   最後までご覧いただき有難うございました。

<記事内容についての告知>VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。