注文書籍データを出版社別シートに分別転記する エクセル

faxhatyudataeyecatch

今回のエクセルVBA作業は、注文用に集めた書籍データを出版社毎にまとめ、追加作成した出版社別シートに転記していきます。
注文書を出版社別に送付するためです。

じゅんぱ店長(@junpa33)です。

今回も引き続き書籍のFAX発注書作成についての解説です。

第2回目に作成したエクセルBOOK「FAX注文書作成.xlsmでの作業となります。

FAX注文書作成するVBAコードの重要部分になります。

作業内容としては、

Webから取り込んだ注文書籍の発注用データを、出版社別に整理整頓します。

FAX注文書の様式に合わせてデータのまとめ直しです。

作成を目指すFAX注文書では、

出版社別に作成、1枚の注文書への記載可能な書籍の銘柄数5種類まで

が、絶対条件になります。

取得した発注用データを出版社別に分別する

faxhatyudata012kai

最初に、VBAコード上ではブック名やシート名を頻繁に表示することになります。

なので、モジュールの初頭にこれらオブジェクト名を変数化(いわば簡単なアルファベットでニックネーム化)しておきます。

これから記述する内容はすべて、「FAX注文書作成.xlsm」の「Module2」で行います。

VBA
Option Explicit

Function wb() As Workbook
    Set wb = Workbooks("FAX注文書作成.xlsm")
End Function

Function wsT() As Worksheet
    Set wsT = wb.Worksheets("抽出データ")
End Function

Function wsS() As Worksheet
    Set wsS = wb.Worksheets("出版社リスト")
End Function

フォルダを作成・管理する

今回のエクセルBOOK編集作業の前に、重要な作業があります。

第3回目の「FAX注文書テンプレート.xlsm」と第2回目の「FAX注文書作成.xlsm」を

一つのフォルダで保管します。

フォルダ名を書籍FAX注文書としてください。(お好きな名前でもOKです。)

「FAX注文書作成.xlsm」にシートを追加設定する

「FAX注文書作成.xlsm」を開いてください。

「Sheet1」の名前を変更

シート選択タブで「Sheet1」の名前を変更して、「操作ボタン」としてください。

新しくシートを追加

新しくシートを追加します。その新しいシート名を「出版社リスト」とします。

faxhatyudata001a

抽出データシートのデータの並び替え

第2回目の作業で書誌データを読み込んで「抽出データ」シートには、注文書籍のデータが一覧表示されています。

このデータは取り込んだ順番で並んでいます。

注文書を作成するには、少々非効率です。注文書は出版社単位になるからです。

そこでまず、

一覧表示されたデータを出版社別に、「抽出データ」シートをソートします。

VBA
Sub ソート()
    Dim Hsrow As Long
        With wsT
          .Select
          Hsrow = .Cells(Rows.Count, 1).End(xlUp).Row
          .Range(.Cells(2, 1), .Cells(Hsrow, 7)) _
                   .Sort Key1:=.Cells(1, 3), order1:=xlAscending
        End With
End Sub

「出版社リスト」シートでの作業

「出版社リスト」シートには、発注先の出版社一覧を作成します。

一覧に表示されるリストは、同じ出版社であっても表示方法の違いで区別されて表示されます。

例えば「新春社」、「新春 社」,「(株)新春社」などすべて区別されます。

社名の表示は完全一致が必要です。

VBA
Sub 出版社名抽出()
    Dim tRange, dRange As Range
    Dim buf, buf2 As Variant
    Dim myDic As Object
    Dim Aho As Variant
    Dim HaRow, i As Long
    Dim myKeys As Variant
        With wsT
            .Select
            Aho = .Range("C2").Value
            HaRow = .Cells(Rows.Count, 6).End(xlUp).Row
        End With
        If HaRow > 2 Then
            Set tRange = wsT.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 wsS
                Set dRange = .Range(.Range("A1"), _
                                    .Range("A" & myDic.Count))
                buf2 = dRange
                myKeys = myDic.keys
                
                If myDic.Count = 1 Then
                    .Range("A1").Value = Aho
                Else
                    For i = 1 To myDic.Count
                    buf2(i, 1) = myKeys(i - 1)
                    Next i
                    dRange = buf2
                End If
            End With
        Else
            wsS.Range("A1").Value = Aho
        End If
    End Sub

このように”Dictionaryオブジェクト”を使って重複を排除したリストを作成します。

出版社別にシートを追加作成

出版社リスト」シートにある出版社別に各出版社シートを追加します。

シートの追加位置は、「出版社リスト」シートの右側から、追加シートが一番右になる様に追加していきます。

VBA
Sub シート作成()
 Dim SuRow, t As Long
 Dim Vn As Variant
    With wsS
       .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
          .Activate
       Next t
    End With
 End Sub

各出版社シートへ注文データを振り分ける

先ほど作った出版社シートへデータを振り分けていきます

VBA
Sub 発注振り分け()
    Dim SuRow, t, HaRow, s, N As Long
    Dim Vn As Variant
        With wsS
            .Activate
            SuRow = .Cells(Rows.Count, 1).End(xlUp).Row
            For t = 1 To SuRow
                Vn = .Range("A" & t).Value
                wsT.Activate
                HaRow = wsT.Cells(Rows.Count, 6).End(xlUp).Row
                s = 1
                For N = 1 To HaRow
                    If wsT.Range("C" & N).Value = Vn Then
                        wsT.Rows(N).Copy Destination:=Worksheets(Vn) _
                                                .Range("A" & s)
                        s = s + 1
                        Worksheets(Vn).Columns("A:G").AutoFit
                    End If
                    wsT.Activate
                Next N
                .Activate
            Next t
        End With
End Sub

プロシージャーをまとめるプロシージャー

今までに記述したマクロを順番に自動実行するマクロを作る

この記事で作成したプロシージャーを、一つのボタン操作で一度に行えるようにします。

4つのコードをまとめるVBAコードを作ります。

VBA
Sub 注文データまとめ()
        Call Module2.ソート
        Call Module2.出版社名抽出
        Call Module2.シート作成
        Call Module2.発注振り分け
        Worksheets("操作ボタン").Select
        Range("A1").Select
End Sub

「操作ボタン」シートに起動ボタンを設置

「操作ボタン」シートに「注文データまとめ」ボタンを作ります。

第2回目に行ったのと同じ作業です。

faxhatyudata007a

出版社別シートに分別転記のまとめ

faxhatyudata013kai

Webから書誌情報を入手し必要な情報を追加してまとめた発注データを、今回の記事で、

出版社へ注文できるデータとしてまとめることが出来ました。

注文先の出版社別シートに、注文書籍データを振り分けましたので、

次回以降はそのデータを、注文書テンプレートに転記する作業に移っていきます。

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

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

けれども、

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

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

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

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

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

重要ワード

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

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

です。

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

ハッキリ言って、

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

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

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

vbastudyeyecatch2 エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

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

エクセルVBA最速理解で必要な知識を集めよう!

エクセルVBA業務ツールで日常の業務改善を行いましょう。

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