残高計算

期間指定なしで全データを計算して得意先の売上累計・消費税累計・入金累計

に更新後残高＝期首残高+売上累計+消費税累計―入金累計を更新する。

標準モジュールに記述します。
Sub 残高計算()
    Dim i As Long
    Dim j As Long
    Dim lastRow As Long
    Dim lastRow1 As Long
    Dim kei As Long
'売上計算
'売上明細から得意先コードと金額を作業に取り出す
    Worksheets("作業").Cells.Clear
    lastRow = Worksheets("売上明細").Cells(Rows.Count, 1).End(xlUp).Row
    j = 1
    For i = 2 To lastRow
        Worksheets("作業").Cells(j, 1) = Worksheets("売上明細").Cells(i, 3)
        Worksheets("作業").Cells(j, 2) = Worksheets("売上明細").Cells(i, 9)
        j = j + 1
    Next
'得意先コードで並び替え
    Range(Cells(1, 1), Cells(lastRow, 2)).Select
    ActiveWorkbook.Worksheets("作業").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("作業").Sort.SortFields.Add Key:=Cells(1, 1), SortOn _
    :=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("作業").Sort
    .SetRange Range(Cells(1, 1), Cells(lastRow, 2))
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
'得意先コードで集計
    Worksheets("作業1").Cells.Clear
    j = 1
    kei = 0
    For i = 1 To lastRow
        kei = kei + Worksheets("作業").Cells(i, 2)
        If Worksheets("作業").Cells(i, 1) <> Worksheets("作業").Cells(i + 1, 1) Then
            Worksheets("作業1").Cells(j, 1) = Worksheets("作業").Cells(i, 1)
            Worksheets("作業1").Cells(j, 2) = kei
        j = j + 1
        kei = 0
      End If
    Next
'得意先の売上累計をクリア
    lastRow = Worksheets("得意先").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        Worksheets("得意先").Cells(i, 8) = ""
    Next
'得意先を集計した作業１のデータを得意先の売上累計に更新
    lastRow1 = Worksheets("作業1").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To lastRow1
        For j = 2 To lastRow
            If Worksheets("作業1").Cells(i, 1) = Worksheets("得意先").Cells(j, 1) Then
               Worksheets("得意先").Cells(j, 8) = Worksheets("作業1").Cells(i, 2)
               Exit For
            End If
        Next
    Next
'消費税計算
'消費税から得意先コードと金額を作業に取り出す
    Worksheets("作業").Cells.Clear
    lastRow = Worksheets("消費税").Cells(Rows.Count, 1).End(xlUp).Row
    j = 1
    For i = 2 To lastRow
        Worksheets("作業").Cells(j, 1) = Worksheets("消費税").Cells(i, 3)
        Worksheets("作業").Cells(j, 2) = Worksheets("消費税").Cells(i, 5)
        j = j + 1
    Next
'得意先コードで並び替え
    Range(Cells(1, 1), Cells(lastRow, 2)).Select
    ActiveWorkbook.Worksheets("作業").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("作業").Sort.SortFields.Add Key:=Cells(1, 1), SortOn _
    :=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("作業").Sort
    .SetRange Range(Cells(1, 1), Cells(lastRow, 2))
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
'得意先コードで集計
    Worksheets("作業1").Cells.Clear
    j = 1
    kei = 0
    For i = 1 To lastRow
        kei = kei + Worksheets("作業").Cells(i, 2)
        If Worksheets("作業").Cells(i, 1) <> Worksheets("作業").Cells(i + 1, 1) Then
            Worksheets("作業1").Cells(j, 1) = Worksheets("作業").Cells(i, 1)
            Worksheets("作業1").Cells(j, 2) = kei
        j = j + 1
        kei = 0
      End If
    Next
'得意先の消費税をクリア
    lastRow = Worksheets("得意先").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        Worksheets("得意先").Cells(i, 9) = ""
    Next
'得意先を集計した作業１のデータを得意先の消費税に更新
    lastRow1 = Worksheets("作業1").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To lastRow1
        For j = 2 To lastRow
            If Worksheets("作業1").Cells(i, 1) = Worksheets("得意先").Cells(j, 1) Then
               Worksheets("得意先").Cells(j, 9) = Worksheets("作業1").Cells(i, 2)
               Exit For
            End If
        Next
    Next
'入金計算
'入金明細から得意先コードと金額を作業に取り出す
    Worksheets("作業").Cells.Clear
    lastRow = Worksheets("入金明細").Cells(Rows.Count, 1).End(xlUp).Row
    j = 1
    For i = 2 To lastRow
        Worksheets("作業").Cells(j, 1) = Worksheets("入金明細").Cells(i, 3)
        Worksheets("作業").Cells(j, 2) = Worksheets("入金明細").Cells(i, 6)
        j = j + 1
    Next
'得意先コードで並び替え
    Range(Cells(1, 1), Cells(lastRow, 2)).Select
    ActiveWorkbook.Worksheets("作業").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("作業").Sort.SortFields.Add Key:=Cells(1, 1), SortOn _
    :=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("作業").Sort
    .SetRange Range(Cells(1, 1), Cells(lastRow, 2))
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
'得意先コードで集計
    Worksheets("作業1").Cells.Clear
    j = 1
    kei = 0
    For i = 1 To lastRow
        kei = kei + Worksheets("作業").Cells(i, 2)
        If Worksheets("作業").Cells(i, 1) <> Worksheets("作業").Cells(i + 1, 1) Then
            Worksheets("作業1").Cells(j, 1) = Worksheets("作業").Cells(i, 1)
            Worksheets("作業1").Cells(j, 2) = kei
        j = j + 1
        kei = 0
      End If
    Next
'得意先の入金累計・残高をクリア
    lastRow = Worksheets("得意先").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        Worksheets("得意先").Cells(i, 10) = ""
        Worksheets("得意先").Cells(i, 11) = ""
    Next
'得意先を集計した作業１のデータを得意先の入金累計に更新
    lastRow1 = Worksheets("作業1").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To lastRow1
        For j = 2 To lastRow
            If Worksheets("作業1").Cells(i, 1) = Worksheets("得意先").Cells(j, 1) Then
               Worksheets("得意先").Cells(j, 10) = Worksheets("作業1").Cells(i, 2)
               Worksheets("得意先").Cells(j, 11) = Worksheets("得意先").Cells(j, 7) + Worksheets("得意先").Cells(j, 8) + Worksheets("得意先").Cells(j, 9) - Worksheets("得意先").Cells(j, 10)
               Exit For
            End If
        Next
    Next
'得意先残高計算=期首残高+売上累計+消費税-入金累計
    For i = 2 To lastRow
        Worksheets("得意先").Cells(i, 11) = Worksheets("得意先").Cells(i, 7) + Worksheets("得意先").Cells(i, 8) + Worksheets("得意先").Cells(i, 9) - Worksheets("得意先").Cells(i, 10)
    Next
    MsgBox "残高計算が終わりました"
End Sub