明細請求書

請求書発行の期間と得意先を入力します。
標準モジュールに記述します。
Sub 明細請求書発行()
    frmMeisai.Show
End Sub

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

フォームモジュールに記述します。

Private Sub cmdJikkou_Click()
    Dim i As Long
    Dim j As Long
    Dim lastRow As Long
   
'売上データの取り出し
    Worksheets("作業").Cells.Clear
'明細請求書明細行クリア
    For i = 16 To 48
        Worksheets("明細請求書").Cells(i, 2) = ""
        Worksheets("明細請求書").Cells(i, 3) = ""
        Worksheets("明細請求書").Cells(i, 4) = ""
        Worksheets("明細請求書").Cells(i, 6) = ""
        Worksheets("明細請求書").Cells(i, 7) = ""
        Worksheets("明細請求書").Cells(i, 8) = ""
    Next
    lastRow = Worksheets("売上明細").Cells(Rows.Count, 1).End(xlUp).Row
    j = 1
    For i = 2 To lastRow
        If Worksheets("売上明細").Cells(i, 2) >= txtKaisi.Text And Worksheets("売上明細").Cells(i, 2) <= txtEnd.Text And Worksheets("売上明細").Cells(i, 3) = txtTcode.Text Then
            Worksheets("作業").Cells(j, 1) = Worksheets("売上明細").Cells(i, 1)
            Worksheets("作業").Cells(j, 2) = Worksheets("売上明細").Cells(i, 2)
            Worksheets("作業").Cells(j, 3) = Worksheets("売上明細").Cells(i, 5)
            Worksheets("作業").Cells(j, 4) = Worksheets("売上明細").Cells(i, 6)
            Worksheets("作業").Cells(j, 5) = Worksheets("売上明細").Cells(i, 7)
            Worksheets("作業").Cells(j, 6) = Worksheets("売上明細").Cells(i, 8)
            Worksheets("作業").Cells(j, 7) = Worksheets("売上明細").Cells(i, 9)
            j = j + 1
        End If
    Next
'ヘッダーのコピー
    lastRow = Worksheets("得意先").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        If Worksheets("得意先").Cells(i, 1) = txtTcode.Text Then
           Worksheets("明細請求書").Cells(2, 7) = txtEnd.Text
           Worksheets("明細請求書").Cells(3, 2) = "〒" & Worksheets("得意先").Cells(i, 3)
           Worksheets("明細請求書").Cells(4, 2) = Worksheets("得意先").Cells(i, 4)
           Worksheets("明細請求書").Cells(6, 2) = Worksheets("得意先").Cells(i, 2) & "様"
           Worksheets("明細請求書").Cells(7, 3) = "コード" & Worksheets("得意先").Cells(i, 1)
           Worksheets("明細請求書").Cells(3, 2) = Worksheets("得意先").Cells(i, 3)
           Worksheets("明細請求書").Cells(13, 2) = Worksheets("得意先").Cells(i, 12)
           Worksheets("明細請求書").Cells(13, 3) = Worksheets("得意先").Cells(i, 15)
           Worksheets("明細請求書").Cells(13, 4) = Worksheets("得意先").Cells(i, 12) - Worksheets("得意先").Cells(i, 15)
           Worksheets("明細請求書").Cells(13, 5) = Worksheets("得意先").Cells(i, 13)
           Worksheets("明細請求書").Cells(13, 6) = Worksheets("得意先").Cells(i, 14)
           Worksheets("明細請求書").Cells(13, 7) = Worksheets("得意先").Cells(i, 16)
        End If
    Next

    lastRow = Worksheets("作業").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To lastRow
        Worksheets("明細請求書").Cells(15 + i, 2) = Worksheets("作業").Cells(i, 1)
        Worksheets("明細請求書").Cells(15 + i, 3) = Worksheets("作業").Cells(i, 2)
        Worksheets("明細請求書").Cells(15 + i, 4) = Worksheets("作業").Cells(i, 3) & Worksheets("作業").Cells(i, 4)
        Worksheets("明細請求書").Cells(15 + i, 6) = Worksheets("作業").Cells(i, 5)
        Worksheets("明細請求書").Cells(15 + i, 7) = Worksheets("作業").Cells(i, 6)
        Worksheets("明細請求書").Cells(15 + i, 8) = Worksheets("作業").Cells(i, 7)
    Next
    Unload Me
    Worksheets("明細請求書").Select
    Worksheets("明細請求書").PrintPreview
End Sub
Private Sub cmdCancel_Click()
    Unload Me
End Sub
Private Sub lstTokui_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    txtTcode = lstTokui.Text
    lblTname = lstTokui.List(lstTokui.ListIndex, 1)
End Sub
Private Sub UserForm_Initialize()
    Dim lastRow As Long
    Dim i As Long
    lastRow = Worksheets("得意先").Cells(Rows.Count, 1).End(xlUp).Row
    lstTokui.ColumnCount = 2
    For i = 2 To lastRow
        With lstTokui
            .AddItem
            .List(i - 2, 0) = Worksheets("得意先").Cells(i, 1)
            .List(i - 2, 1) = Worksheets("得意先").Cells(i, 2)
        End With
    Next
End Sub