[[20180806185001]]　『請求書を月別に新規ファイルへ』（勘太郎）　ページの最後に飛ぶ

『請求書を月別に新規ファイルへ』（勘太郎）

 お世話になります。
請求書を作成&印刷する仕事をしています。
データシートに入ったデータを雛形の請求書シートにはめ込んで
シート作成を自動で行う方法を考えています。

【データシート】
　 A B C D
１日付顧客名商品名価格
２ 201808 田中鉛筆 100
３ 201808 鈴木消しゴム 1000
４ 201809 石田鉛筆 100
･
･
･

こちらのサイトを参考にさせていただきながら作成したのですが
シートを作るところまでで、躓いてしまいました。

1)指定した日付(月毎)にシートを作成したい。
2)作成したシートは新しいブックで日付+請求書という名前で保存したい。

この二点について、下記のコードをどの様に追記すれば良いか
ご教授いただけますか。

よろしくお願いします。

Sub test()

     Dim i As Long, cnt As Long
     Application.ScreenUpdating = False
     For i = 2 To Sheets("データ").Cells(Rows.Count, 1).End(xlUp).Row
         Sheets("請求書").Copy After:=Sheets(Sheets.Count)
         cnt = Sheets.Count
         Sheets(cnt).Name = Sheets("データ").Cells(i, 1).Value
         Sheets("データ").Cells(i, 2).Copy Sheets(cnt).Range("A12:G12")
         Sheets("データ").Cells(i, 3).Copy Sheets(cnt).Range("C28:I28")
         Sheets("データ").Cells(i, 4).Copy Sheets(cnt).Range("C33")
     Next
     Sheets("データ").Select
     Application.ScreenUpdating = True
 End Sub

＜使用 Excel：Excel2010、使用 OS：Windows10 ＞

＞指定した日付(月毎)にシートを作成したい。

顧客が混在してもよいのでしょうか？

（マナ） 2018/08/06(月) 20:40

＞作成したシートは新しいブックで日付+請求書という名前で保存したい。

ということであれば、シートは新規ブックにコピーしてはどうでしょうか。
下記の違いがわかりますか。

 Sub test1()
    Sheets("請求書").Copy After:=Sheets(Sheets.Count)
 End Sub

 Sub test2()
    Sheets("請求書").Copy
 End Sub

（マナ） 2018/08/06(月) 21:01

マナさんお返事ありがとうございます。
顧客名は月毎に１行しか無いので混在することはありません。

テスト1は同ブック内でシートを新たに作ってコピー貼り付け
テスト2はシート自体をコピー(貼り付け無し)

でしょうか？(あまり自信がありませんが。。。)
（勘太郎） 2018/08/06(月) 21:43

＞テスト1は同ブック内でシートを新たに作ってコピー貼り付け

指定の位置（この場合はブック内）にシート自体をコピー(貼り付け無し)

＞テスト2はシート自体をコピー(貼り付け無し)

新規ブックにシート自体をコピー(貼り付け無し)

です。
請求書ごとに

＞新しいブックで日付+請求書という名前で保存したい

とういうことであれば、テスト２を使うとよいです。

＞顧客名は月毎に１行しか無いので混在することはありません。

＞２ 201808 田中鉛筆 100
＞３ 201808 鈴木消しゴム 1000

２行目の田中さんと３行目の鈴木さんは、どちらも８月ですが？
ブック名は、201808請求書と同じになってしまします。
同じ名前で保存することはできませんが、どうしますか。

（マナ） 2018/08/07(火) 19:06

マナさんお返事ありがとうございます。
今出先なんで、家に帰ってからしっかり読ませていただきます。
取り急ぎお返事を

テスト、どちらも貼り付け無しなんですね。
すいません、理解が追いつかず...
２行目と3行目ですが、
201808請求書ブックの中にシート名が田中と鈴木になるようにしたいのです。
説明が上手くできずすいません。

（勘太郎） 2018/08/07(火) 21:08

たぶんすれ違いが起こってます。
請求書と仰っているので、マナさんは顧客の月ごとにブックを分けて保存したい。という質問と理解されてるのでは？
また、それにたいして質問者さんは、請求データベースから月ごとに抽出してブックを分けて取り出したいということでは？
（もこな２） 2018/08/07(火) 21:58

このような場合はないのでしょうか？

＞201808 田中鉛筆 100
＞201808 田中消しゴム 1000

（マナ） 2018/08/07(火) 22:14

もこな2さん、代弁頂いて有難うございます。
その通りです。
マナさん、何度もすいません。
同じ月に顧客名が2行になる事は有りません。

月初に、前月分の請求書は発行すべく前月分に該当する行分を
雛形の請求書シートにコピーして行数分のシートを新規ブック作成し
201808請求書.xlxsといった感じに名前を付けて保存したいのです。

少しでも自動化できれば助かります。

（勘太郎） 2018/08/07(火) 22:50

とりあえず、
(1)オートフィルタで「201808」のものを抽出する。
(2)新規ブックを立ち上げる
(3)(1)で抽出したものを(2)に張り付ける
(4)(3)を「201808請求書.xls」っていう名前で保存する。

って感じじゃだめですか？
（もこな２） 2018/08/07(火) 22:58

ということは、前月分のブックを作成するだけですね。
必ず前月データがあるならば、こんな感じでしょうか。

 Option Explicit

 Sub test()
    Dim wsT As Worksheet
    Dim p As String
    Dim tbl As Range
    Dim r As Range, c As Range
    Dim 月 As String

     Set wsT = Sheets("請求書")
     p = ThisWorkbook.Path & "\"
     Set tbl = Sheets("データ").Range("a1").CurrentRegion
     月 = Format(WorksheetFunction.EDate(Date, -1), "yyyymm")

     With Workbooks.Add(xlWBATWorksheet)
        tbl.AutoFilter 1, 月
        Set r = Intersect(tbl.Offset(1), tbl.Columns(1))
        For Each c In r.SpecialCells(xlCellTypeVisible)
            wsT.Copy after:=.Sheets(1)
            With ActiveSheet
                .Name = c.Offset(, 1)
                c.Offset(, 1).Copy .Range("A12:G12")
                c.Offset(, 2).Copy .Range("C28:I28")
                c.Offset(, 3).Copy .Range("C33")
            End With
        Next
        tbl.AutoFilter
        Application.DisplayAlerts = False
        .Sheets(1).Delete
        Application.DisplayAlerts = True
        .SaveAs p & 月 & "請求書.xlsx", xlOpenXMLWorkbook
        .Close
    End With

 End Sub

（マナ） 2018/08/07(火) 23:18

マナさん、ありがとうございます！！
感動です！

一瞬で理想のファイルが作成されました！

一点、お願いがあるのですが、たまーに前々月分にミスがあって作成し直し
が発生します。

作成したい月を選択か入力して選べるように・・・とか出来ますでしょうか？

(Data,-1を-2で起動したら前々月分も作れたのですが)

何度もすいません。
よろしくお願いいたします。
（勘太郎） 2018/08/08(水) 12:50

 Application.InputBox　を勉強されるとよいですよ。　＾＾
https://msdn.microsoft.com/ja-jp/vba/excel-vba/articles/application-inputbox-method-excel
http://www.moug.net/tech/exvba/0010014.html
（隠居じーさん） 2018/08/08(水) 20:21

＞作成したい月を選択か入力して選べるように・・・とか出来ますでしょうか？

その前に、このマクロは、どのようにして実行させたいですか。
マクロを登録したボタンのクリック？

'-----
今更ですが

Wordの差し込み印刷を使うと、
選んだ月のみの請求書を１つの文書に作成できます。
顧客毎で１つの文書にすることもできます。
名前をつけて保存を自動ではできませんが
何よりマクロを使わなくてもよいのがメリットです。

（マナ） 2018/08/08(水) 21:06

隠居じーさん、マナさんお返事ありがとうございます。

リンクありがとうございます。
読んで考えてみます。

マクロの実行は、コマンドボタンにマクロを登録して実行させています。
今はコマンドボタンを押すと前月分の作成を自動で行ってくれていますが、
インプットボックス？に201806と入力してOKを押すとその月分ファイルが
出来れば良いな、と思っています。

ワードの差し込み印刷はやったことがありませんので、知りませんでした。
エクセルデータからワードファイルが出来るのでしょうか？
マクロ無しで出来るのはすごい魅力的ですね。
一度調べてみます。
（勘太郎） 2018/08/08(水) 21:38

ボタンがデータシートにあるとして
例えば、こんな方法もあります。

 Set tbl = Sheets("データ").Range("a1").CurrentRegion
 月 = Format(WorksheetFunction.EDate(Date, -1), "yyyymm")
 　　↓		
 Set tbl = Range("a1").CurrentRegion
 If Intersect(ActiveCell, tbl) Is Nothing Then
    MsgBox "作成する月のデータをどれか１つ選んでから実行してください"
    Exit Sub
 End If
 月 = ActiveCell.EntireRow.Range("a1").Value

（マナ） 2018/08/08(水) 21:56

おお　＾＾
画像作成ソフトのスポイトみたいっすね。
いつもながら
大変勉強になりますです。

（隠居じーさん） 2018/08/08(水) 22:33

マナさん、ありがとうございました！
理想すぎるほど作業が楽になりました！

本当にありがとうです！

同様のファイルが１７ファイルもあったので、毎月
半日以上かかっていたのが、30分で終わりました！
（勘太郎） 2018/08/09(木) 22:34

コメント返信：

[ 一覧(最新更新順) ]

[[20180806185001]] 『請求書を月別に新規ファイルへ』（勘太郎） ページの最後に飛ぶ

[[20180806185001]]　『請求書を月別に新規ファイルへ』（勘太郎）　ページの最後に飛ぶ