じゅんぱ店長（@junpa33）です。
今回も引き続き書籍の注文書の作成について解説していきます。
今回は前々回作成のエクセルBOOK「FAX注文書作成.xlsm」を中心に加工していきます。
Fax注文書作成するVBAコードの最重要部分になります。
取り込んできた注文書籍のデータを、次の作業として出版社別に振り分けてまとめて行きます。
最終的に、１枚のFAX注文書の注文先は出版社１社のみである必要があるからです。
FAX注文書作成の関連の記事はこちらになります。
コンテンツ
出版社別に自動で振り分けた書籍データで注文書を作成する
フォルダを作成する
今回のエクセルBOOK編集作業の前に、重要な作業があります。
前回作成の「FAX注文書テンプレート.xlsm」と前々回作成の「FAX注文書作成.xlsm」を
一つのフォルダで保管します。
フォルダ名を「書籍FAX注文書」としてください。（お好きな名前でもＯＫです。）
「FAX注文書作成.xlsm」を開く
「FAX注文書作成.xlsm」を開いてください。
「Sheet1」の名前を変更
シート選択タブで「Sheet1」の名前を変更して、「操作ボタン」としてください。
新しくシートを追加する
新しくシートを追加します。その新しいシート名を「出版社リスト」とします。
この様な感じになります。
VBAコードの核心部分。注文内容を出版社別に振り分ける
先回の作業で書誌データを読み込んで「抽出データ」シートに一覧表示させました。
このデータは取り込んだ順番に並んでいます。
ですので、そのまま注文書テンプレートに転記するには非常に非効率です。
そこで、一覧表示されたデータを出版社別に”各出版社シート”を作り仕分けをしていきます。
「抽出データ」をソートします
ソートキーを”出版社名”として並び替えをします。
VBAコードを記入しますので、module２を挿入追加してください。
追加方法がイマイチと言われる場合は、
先記事の「ＶＢＥ（ビジュアルベーシックエディター）を起動する」で確認してください。
こちらも参考記事です。
コードはこのようになります。
コード①
Sub ソート()
Dim Hsrow As Long
Worksheets(“抽出データ”).Activate
Hsrow = Cells(Rows.Count, 1).End(xlUp).Row
Worksheets(“抽出データ”).Range(Cells(2, 1), Cells(Hsrow, 7)) _
.Sort Key1:=Worksheets(“抽出データ”).Cells(1, 3), order1:=xlAscending
End Sub
出版社リストを作ります
「出版社リスト」シートに出版社の一覧を表示させます。
一覧に表示されるリストは、同じ出版社であっても表示方法の違いで区別されて表示されます。
例えば「新春社」、「新春 社」,「（株）新春社」などすべて区別されます。
社名の表示は完全一致です。（通常のエクセルシートのソートの癖と同じです。）
コードは少し長いですがこのような感じです。
このように”Dictionaryオブジェクト”を使って重複を排除したリストを作成します。
重複データの整理についてはこちらの記事を参考にしてください。
エクセルVBA 同じ項目をまとめる（重複データの整理）コード作成
With～End Withの使い方については、この記事を参考にしてください。
コード②
Sub 出版社名抽出()
Dim tRange As Range, dRange As Range
Dim buf As Variant, buf2 As Variant
Dim myDic As Object
Dim i As Long
Dim myKeys As Variant
Worksheets(“抽出データ”).Select
Aho = Worksheets(“抽出データ”).Range(“C2”).Value
HaRow = Cells(Rows.Count, 6).End(xlUp).Row
If HaRow > 2 Then
Set tRange = Sheets(“抽出データ”).Range(“C2:C” & HaRow)
buf = tRange
Set myDic = CreateObject(“Scripting.Dictionary”)
On Error Resume Next
For i = 1 To HaRow
myDic.Add buf(i, 1), “”
Next i
On Error GoTo 0
With Sheets(“出版社リスト”)
Set dRange = .Range(.Range(“A1”), .Range(“A” & myDic.Count))
End With
buf2 = dRange
myKeys = myDic.keys
If myDic.Count = 1 Then
Worksheets(“出版社リスト”).Range(“A1”).Value = Aho
Else
For i = 1 To myDic.Count
buf2(i, 1) = myKeys(i – 1)
Next i
dRange = buf2
End If
Else
Worksheets(“出版社リスト”).Range(“A1”).Value = Aho
End If
End Sub
出版社別のシートを作成
「出版社リスト」にある出版社別にシートを新しく追加します。
コード③
Sub シート作成()
Worksheets(“出版社リスト”).Activate
SuRow = Cells(Rows.Count, 1).End(xlUp).Row
For t = 1 To SuRow
Vn = Range(“A” & t).Value
Worksheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Vn
Worksheets(“出版社リスト”).Activate
Next t
End Sub
Worksheetsの追加時に行うポイントは、
を参考にできます。
各出版社シートへのデータ振り分け
先ほど作った出版社シートへデータを振り分けていきます。
For～Nextの入れ子構造については、
エクセルVBA！For～Nextのループと入れ子構造をVBA最速理解
を参考してください。
コード④
Sub 発注振り分け()
Worksheets(“出版社リスト”).Activate
SuRow = Cells(Rows.Count, 1).End(xlUp).Row
For t = 1 To SuRow
Vn = Range(“A” & t).Value
Worksheets(“抽出データ”).Activate
HaRow = Cells(Rows.Count, 6).End(xlUp).Row
s = 1
For N = 1 To HaRow
If Range(“C” & N).Value = Vn Then
Rows(N).Copy Destination:=Worksheets(Vn).Range(“A” & s)
s = s + 1
Worksheets(Vn).Columns(“A:G”).AutoFit
End If
Worksheets(“抽出データ”).Activate
Next N
Worksheets(“出版社リスト”).Activate
Next t
End Sub
ここまでのVBA動作を纏める
VBAコード①から④を一つのボタン操作で一度に行えるようにします。
「操作ボタン」シートに「注文データまとめ」ボタンを作ります。
4つのコードを纏めるVBAコードを作ります。
コード⑤
Sub 注文データまとめ()
ソート
出版社名抽出
シート作成
発注振り分け
Worksheets(“操作ボタン”).Select
Range(“A1”).Select
End Sub
次に「操作ボタン」シートにボタンを設置します。
先回に行ったのと同じ作業です。
作業確認は「データ抽出ボタンを設置する」でご確認ください。
こんな感じになります。
FAX注文書テンプレートBOOKと連携する
ここで作成した注文書のデータを「FAX注文書テンプレート.xlsm」に送り、連携をセットします。
FAX注文書テンプレートの”Sheet2”のシート名を変更する
「FAX注文書テンプレート.xlsm」を開き、”Sheet2”のシート名を「発注資料表」としてください。
注文データのBOOK間移動をセットする
「FAX注文書作成.xlsm」に戻ります。
この「FAX注文書作成.xlsm」のVBAマクロで「FAX注文書テンプレート.xlsm」のシートのデータをコントロールします。
先ほど名前変更した「FAX注文書テンプレート.xlsm」の「発注資料表」に作表します。
BOOKとシートを行ったり来たりしますので、記号化してコードを書いていきます。
VBAコードをmodule３に記入します。モジュールの挿入追加をしてください。
追加方法の再度の確認は
先記事の「ＶＢＥ（ビジュアルベーシックエディター）を起動する」です。
テンプレートにある様に出版社（発注先）ごと、5品目ごとに区分けして４０ページまで作ることができます。
「 For t = 1 To 40 」のところで 40を50にすれば５０ページになります。ご自由に変更ください。
「発注資料表」シートのＭ列は、いまどこまでページ数を使っているかを調べる”マーカー”を挿入しています。
文字・記号などを書き入れないようにしてください。
コードは以下のようになります。
For～Nextの使い方については、この記事も参考にしてください。
エクセルVBA！For～Nextのループと入れ子構造をVBA最速理解
If条件文についてはこの記事を参考にしてください。
「If条件文」のVBAコードの組み方。条件の絞り方を最速に理解。
ChDirの使い方についてはこちらの記事が参考になります。
「For Each～Next」の使い方についてはこちらの記事を参考にしてください。
コード⑥
Sub 発注資料()
Dim Hsheet As Worksheet
Dim Ssheet As Worksheet
Dim SPRow As Long
Dim VARow As Long
Dim HBRow As Long
Dim VA As Variant, Hva As Variant, Fva As Variant, Iva As Variant
Dim Bva As Variant, Jva As Variant, Gva As Variant, Nva As Variant
Dim i As Long, j As Long, x As Long, xx As Long, y As Long, t As Long, tt As Long
Dim w As Long, ww As Long, z As Long, AR As Long
ChDir ThisWorkbook.Path
Workbooks.Open Filename:="FAX注文書テンプレート.xlsm"
Set Hsheet = Workbooks("FAX注文書テンプレート.xlsm").Worksheets("発注資料表")
Set Ssheet = Workbooks("FAX注文書作成.xlsm").Worksheets("出版社リスト")
Ssheet.Activate
SPRow = Cells(Rows.Count, 1).End(xlUp).Row
Hsheet.Activate
For t = 1 To 40 '４０ページまで 50にすれば５０ページまで可
tt = t * 9 - 8
Range("A" & tt) = "Ｐ" & t
Range("A" & tt + 1) = "分類名"
Range("B" & tt + 1) = "出版社名"
Range("C" & tt + 1) = "書名"
Range("D" & tt + 1) = "著者名"
Range("E" & tt + 1) = "冊数"
Range("F" & tt + 1) = "本体価格"
Range("G" & tt + 1) = "シリーズ名"
Range("H" & tt + 1) = "版数"
Range("I" & tt + 1) = "送品条件"
Range("J" & tt + 1) = "ＩＳＢＮコード"
Range("K" & tt + 1) = "客注名"
Range("M" & 1) = "この列文字記入禁止"
With Range(Cells(tt, 1), Cells(tt + 1, 13))
.Font.Bold = True
.Interior.ColorIndex = 35
End With
Next t
Ssheet.Activate
z = 1
For i = 1 To SPRow
VA = Range("A" & i).Value
Worksheets(VA).Activate
VARow = Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To VARow
Hva = Range("C" & j).Value
Fva = Range("B" & j).Value
Iva = Range("D" & j).Value
Bva = Range("F" & j).Value
Jva = Range("E" & j).Value
Gva = Range("A" & j).Value
Nva = Range("G" & j).Value
Hsheet.Activate
AR = WorksheetFunction.Match("Ｐ" & z, Hsheet.Columns("A"), 0)
x = j Mod 5
xx = j \ 5
If x = 0 Then xx = j \ 6
y = AR + 1 + j + 4 * xx
Range("B" & y).Value = Hva '出版社名
Range("C" & y).Value = Fva '書名
Range("D" & y).Value = Iva '著者名
Range("E" & y).Value = Bva '冊数
Range("F" & y).Value = Jva '本体価格
Range("J:J").NumberFormatLocal = "0_ "
Range("J" & y).Value = Gva 'ISBNコード
Range("K" & y).Value = Nva '客注名
Range("M" & y).Value = "○" 'マーキング
Workbooks("FAX注文書作成.xlsm").Activate
Worksheets(VA).Select
Next j
Hsheet.Activate
HBRow = Cells(Rows.Count, 13).End(xlUp).Row
w = HBRow Mod 9
ww = HBRow \ 9
If ww = 0 Then
If w < 3 Then
z = 1
Else
z = 2
End If
Else
z = ww + 1 + 1
End If
Workbooks("FAX注文書作成.xlsm").Activate
Worksheets("出版社リスト").Select
Next i
Hsheet.Activate
Hsheet.Columns("A:M").AutoFit
End Sub
複数に跨ってエクセルBOOKを操作しますので、注意点があります。
ですので、すでに「FAX注文書テンプレート.xlsm」が開いていれば”VBAのBOOK オープンの命令”をスルーして次の作業に移らせるようにします。この修正コードはこのようになります。なお変数宣言のコードの下側にある
ChDir ThisWorkbook.Path
Workbooks.Open Filename:=”FAX注文書テンプレート.xlsm”
は削除してください。
コード⑥修正
Dim flag As Boolean
Dim Wb As Workbook
Dim Fpa As String
ChDir ThisWorkbook.Path
Fpa = ThisWorkbook.Path & "\FAX注文書テンプレート.xlsm"
flag = False
For Each Wb In Workbooks
If Wb.FullName = Fpa Then
flag = True
Exit For
End If
Next Wb
If flag = False Then
Workbooks.Open Fpa
End If
この様になります。
このVBAマクロのボタンを設置します。
「操作ボタン」シートに「発注資料作成」ボタンを作ります。
先ほどと同じようにしてください。
データクリアボタンの設置
VBAコードの設置は新しいモジュール（module４）に記入します。
まず挿入設置してください。
「FAX注文書作成.xlsm」BOOKを使用後に、最初のデフォルト状態に戻すようにします。
データが入力された各シートと出版社ごとのシートをすべて削除します。
抽出データをクリアーする
最初にクリアーするのは「抽出データ」シートです。
コード⑦
Sub 抽出データクリアー()
Worksheets("抽出データ").Select
Cells.Select
Selection.Clear
Range("A1").Select
Worksheets("操作ボタン").Select
Range("A1").Select
End Sub
出版社別シートを削除する
作成順で行うと「出版社リスト」になります。
しかしこれを先にクリアーすると、”出版社別シート”を削除するVBAコードの設計が面倒です。
コード⑧
Sub 出版社シートクリアー()
Dim PrRow as Long
Dim PrV as Variant
Worksheets("出版社リスト").Activate
If Range("A1").Value <> "" Then
PrRow = Cells(Rows.Count, 1).End(xlUp).Row
For t = PrRow To 1 Step -1
PrV = Range("A" & t).Value
Worksheets(PrV).Select
Application.DisplayAlerts = False
Worksheets(PrV).Delete
Worksheets("出版社リスト").Activate
Next t
Application.DisplayAlerts = True
End If
End Sub
出版社リストをクリアーする
そして出版社リストをクリアーします。
コード⑨
Sub 出版社リストクリアー()
Worksheets("出版社リスト").Select
Cells.Select
Selection.Clear
Range("A1").Select
Worksheets("操作ボタン").Select
Range("A1").Select
End Sub
クリアーボタンを設置する
コード⑦から⑨のＶＢＡを順に実行する「ALLクリア」ＶＢＡコードとそのボタンを設置します。
コード⑩
Sub ALLクリアクリアー()
抽出データクリアー
出版社シートクリアー
出版社リストクリアー
End Sub
この様になります。
書籍データを出版社別に注文書を作成。
VBAコードの核心部分のまとめ
これで「FAX注文書作成.xlsm」の方は完成です。
操作方法についてですが、
- 「書誌データ貼付」シートにWebからの書籍情報をコピーペーストします。”形式を選択して貼り付け”で”テキスト”を選択します。
- 「抽出項目・抽出セル名表」に必要データのセル位置を入力します。
- 「書誌データ抽出ボタン」を押すと、「抽出データ」シートに情報が貼り付けられます。
- 「操作ボタン」シートに移動し「注文データまとめ」ボタンを押せば、情報が出版社別に仕分けされます。
- 「発注資料作成」ボタンを押せば、「FAX注文書テンプレート.xlsm」に情報が送られます。
- 最後に「ALLクリアー」ボタンで使用前のデフォルト状態に戻ります。終了時にはこの状態で保存終了してください。
次回の記事は「FAX注文書テンプレート.xlsm」での作業となります。
短期間でエクセルVBAの独学習得を目指したいなら
エクセルVBAを独学する独習方法は、学習者それぞれ十人十色、多種多様と思われます。
けれども、
出来るだけ効率よく学習するためには、いくつかの大切なポイントがあります。
独学でもVBA習得の中級クラスに達するのはそんなに難しいことではありません。
先人が行った勉強方法をあなたがそのまま利用すればよいということです。
独習のための大切な7つのポイントは、上記記事にて解説しています。
独習によるVBA習得のキーワードは、
出来るだけ多くの実例に触れること！
です。
正直、VBAの学習について自分の周りの仕事（業務）からだけ実例を得るのでは効率良い習熟は無理です。
ハッキリ言って、
本当に短い期間でVBA習得を成功させたいなら、今使っている参考書が良書かどうかを判断し、新ツールとしてオンライン学習も取り入れて行うことが、
手っ取り早く短期間習得できるというのは間違いないでしょう。
エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。
エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です
エクセルVBAを使って業務効率を上げて行くのに、始めのうちに知っておきたい内容を纏めています。
今回の記事はここまでです。 最後までご覧いただき有難うございました。
