【販売管理システム作成】見積書を自動作成する方法①【シートをコピーしてデータ転記】

2019年8月21日
2019年9月15日
販売管理システム【見積編】

販売管理システム【見積編】

みなさん、こんにちはケンケンです。

前回から販売管理システムの完成を目指しています。

今回からは、VBAを使って見積書を自動作成する方法をご紹介します。

以前に請求書を自動作成する方法をご紹介していますので、そちらも合わせて確認していただけると幸いです。

それでは、よろしくお願いします。

1 見積書をコピーしてデータを流し込む
2 まとめ

見積書をコピーしてデータを流し込む

見積書をコピーする

まずは、前回ダウンロードした見積書のテンプレートを別シートにコピーします。

コピーした見積書にデータを流していくので先にコピーしていきましょう。

標準モジュールに以下のようにコードを書いていきましょう。

Sub data_tenki()
    Sheets("見積書(元)").Copy after:=Sheets(Sheets.Count)
End Sub

Sheetオブジェクトに対してCopyメソッドを使います。

after以降でシートのコピー先を指定します。

after:=Sheets(Sheets.Count)はファイル内にあるシートの枚数を数えてその後ろを指定しています。

つまり、ファイルの最後尾を表現しています。

実行して以下のようにコピーが作成されることを確認しましょう。

コピーした見積書にデータを転記する

コピー出来たらデータを転記していきましょう。

以下のコードを追加してください。

Sub data_tenki()
    Sheets("見積書(元)").Copy after:=Sheets(Sheets.Count)
    ActiveSheet.Range("F6").Value = Sheets("会社情報").Range("A2").Value '①
    ActiveSheet.Range("F7").Value = Sheets("会社情報").Range("B2").Value '②
    ActiveSheet.Range("G7").Value = Sheets("会社情報").Range("C2").Value '③
    ActiveSheet.Range("F8").Value = Sheets("会社情報").Range("D2").Value '④
    ActiveSheet.Range("F9").Value = "TEL " & Sheets("会社情報").Range("E2").Value & " :FAX " & Sheets("会社情報").Range("F2").Value '⑤
    ActiveSheet.Range("H3").Value = Sheets("見積データ一覧").Range("A2").Value '⑥
    ActiveSheet.Range("H4").Value = Sheets("見積データ一覧").Range("B2").Value '⑦
    ActiveSheet.Range("B16:G18").Value = Sheets("見積データ一覧").Range("D2:I4").Value '⑧
    ActiveSheet.Range("G38").Value = Sheets("見積データ一覧").Range("L2").Value '⑨
End Sub

解説していきます。

①～⑤会社情報シートの各データをコピーされた見積書に流し込んでいます。

会社情報はあなたの会社データのことです。

コードを画像でイメージすると以下のようになります。

特に問題ないかと思いますが、あくまでデータを流し込む先は、コピーされた見積書です。今回はActivesheet＝コピーされた見積書として表現しています。

⑤はTELとFAXの文字列を合算しています。

⑥～⑨見積データ一覧シートの見積もりデータを転記しています。

イメージは以下の通りです。

ここで２つ工夫をしています。

ダウンロードした見積書をよく見たところ、品名の欄はB列とC列が結合されていました。

流し込むデータは元データから品名から金額まで範囲指定で一気に転記したいので、このままではうまく転記できません。

そこで、転記元の見積データ一覧シートに空白列を設けました。これが一つ目の工夫です。

２つ目は見積書に転記する消費税を明記するためのデータが欲しかったので、見積データ一覧のL列に消費税率を表記できるようにしました。

関数は以下のように入力します。数式の意味が分からない方は前回の復習をしておきましょう。

=IF(B2>43738,”0.1″,”0.08″)

ここまでできたらコードを実行します。

データが過不足なく転記できて、消費税や合計金額の計算まで問題なければ成功です。

図を張り付けてみよう

しかし、まだ完成ではありません。

左上の先方宛名が正しく表記されていません。

テンプレートをダウンロードしてから気づいたのですが、この部分は図が張り付けられていました。

つまり、データをそのまま転記することができなかったのです。

データを転記する方法に変更してしまえばすぐ済む話ですが、ちょうどよい機会なのでVBAを使って図を張り付けてみましょう。

図を張り付けるためには元となるデータ範囲が必要ですね。

その元になるデータを作るために「得意先貼付元」シートを作成し、そこに得意先データを転記します。

そのコードは以下の通りです。

Sub data_tenki()
    Sheets("見積書(元)").Copy after:=Sheets(Sheets.Count)
    ActiveSheet.Range("F6").Value = Sheets("会社情報").Range("A2").Value
    ActiveSheet.Range("F7").Value = Sheets("会社情報").Range("B2").Value
    ActiveSheet.Range("G7").Value = Sheets("会社情報").Range("C2").Value
    ActiveSheet.Range("F8").Value = Sheets("会社情報").Range("D2").Value
    ActiveSheet.Range("F9").Value = "TEL " & Sheets("会社情報").Range("E2").Value & " :FAX " & Sheets("会社情報").Range("F2").Value
    ActiveSheet.Range("H3").Value = Sheets("見積データ一覧").Range("A2").Value
    ActiveSheet.Range("H4").Value = Sheets("見積データ一覧").Range("B2").Value
    ActiveSheet.Range("B16:G18").Value = Sheets("見積データ一覧").Range("D2:I4").Value
    ActiveSheet.Range("G38").Value = Sheets("見積データ一覧").Range("L2").Value
    
    
    Sheets("得意先貼付元").Range("B1").Value = "〒" & Sheets("得意先一覧").Range("C2").Value '⑩
    Sheets("得意先貼付元").Range("B2").Value = Sheets("得意先一覧").Range("D2").Value '⑪
    Sheets("得意先貼付元").Range("B3").Value = Sheets("得意先一覧").Range("E2").Value '⑫
    Sheets("得意先貼付元").Range("B4").Value = Sheets("得意先一覧").Range("B2").Value & " 御中" '⑬

End Sub

解説していきます。

⑩～⑬得意先一覧シートの「株式会社ABC」のデータを得意先貼付元シートに転記しています。

イメージは以下の通りです。

コードを実行してデータが転記されることを確認してください。

ここまでできたら見積書に図を張り付けてみます。

以下のコードを追加しましょう。

Sub data_tenki()
    Sheets("見積書(元)").Copy after:=Sheets(Sheets.Count)
    ActiveSheet.Range("F6").Value = Sheets("会社情報").Range("A2").Value
    ActiveSheet.Range("F7").Value = Sheets("会社情報").Range("B2").Value
    ActiveSheet.Range("G7").Value = Sheets("会社情報").Range("C2").Value
    ActiveSheet.Range("F8").Value = Sheets("会社情報").Range("D2").Value
    ActiveSheet.Range("F9").Value = "TEL " & Sheets("会社情報").Range("E2").Value & " :FAX " & Sheets("会社情報").Range("F2").Value
    ActiveSheet.Range("H3").Value = Sheets("見積データ一覧").Range("A2").Value
    ActiveSheet.Range("H4").Value = Sheets("見積データ一覧").Range("B2").Value
    ActiveSheet.Range("B16:G18").Value = Sheets("見積データ一覧").Range("D2:I4").Value
    ActiveSheet.Range("G38").Value = Sheets("見積データ一覧").Range("L2").Value
    
    
    Sheets("得意先貼付元").Range("B1").Value = "〒" & Sheets("得意先一覧").Range("C2").Value
    Sheets("得意先貼付元").Range("B2").Value = Sheets("得意先一覧").Range("D2").Value
    Sheets("得意先貼付元").Range("B3").Value = Sheets("得意先一覧").Range("E2").Value
    Sheets("得意先貼付元").Range("B4").Value = Sheets("得意先一覧").Range("B2").Value & " 御中"
    

    Sheets("得意先貼付元").Range("B1:B4").Copy '⑭
    ActiveSheet.Range("B2").Select '⑮
    ActiveSheet.Pictures.Paste.Select '⑮

End Sub

得意先貼付元シートから見積書左上の宛名に得意先データを転記します。

⑭得意先貼付元シートのB1からB4をコピーします。以下の図で確認して下さい。