Sub Button2_Click()
Worksheets("input").Unprotect Password:="nottherealpasswordwasjustquicker"
Worksheets("cashbook").Unprotect Password:="nottherealpasswordwasjustquicker"
Dim NewRow As Variant
Dim TransNo As Integer
Dim lRowLoop As Long
Dim lSheet As Long
Dim rngCellLoop As Range
NewRow = Worksheets("input").Range("D12:BB12")
For lRowLoop = LBound(NewRow, 1) To UBound(NewRow, 2)
NewRow(1, lRowLoop) = NewRow(1, lRowLoop) + 1
Next lRowLoop
TransNo = Worksheets("input").Range("D13").Value + 1
With Worksheets("cashbook")
Worksheets("input").Range("B5:B10").Copy
.Cells(NewRow(1, LBound(NewRow, 2)), 2).PasteSpecial xlValues, Transpose:=True
.Cells(NewRow(1, LBound(NewRow, 2)), 8).Value = .Cells(NewRow(1, LBound(NewRow, 2)) - 1, 8).Value + .Cells(NewRow(1, LBound(NewRow, 2)), 6).Value - .Cells(NewRow(1, LBound(NewRow, 2)), 7).Value
.Cells(NewRow(1, LBound(NewRow, 2)), 1).Value = Worksheets("input").Range("D13").Value
End With
lSheet = Worksheets("input").Range("B6").Value
With Worksheets("P" & lSheet)
.Unprotect Password:="nottherealpasswordwasjustquicker"
Worksheets("input").Range("B5:B10").Copy
.Cells(NewRow(1, LBound(NewRow, 2) + lSheet), 2).PasteSpecial xlValues, Transpose:=True
For Each rngCellLoop In .Range(.Cells(NewRow(1, LBound(NewRow, 2) + lSheet), 2), .Cells(NewRow(1, LBound(NewRow, 2) + lSheet), 8))
rngCellLoop.Value = rngCellLoop.Value * -1
Next rngCellLoop
.Cells(NewRow(1, LBound(NewRow, 2) + lSheet), 8).Value = .Cells(NewRow(1, LBound(NewRow, 2) + lSheet) - 1, 8).Value + .Cells(NewRow(1, LBound(NewRow, 2) + lSheet), 6).Value - .Cells(NewRow(1, LBound(NewRow, 2) + lSheet), 7).Value
.Cells(NewRow(1, LBound(NewRow, 2) + lSheet), 1).Value = Worksheets("input").Range("D13").Value
Worksheets("input").Range("D12").Offset(0, lSheet).Value = NewRow(1, LBound(NewRow, 2) + lSheet)
.Protect Password:="nottherealpasswordwasjustquicker"
End With
MsgBox "New Data added", vbOKOnly, "Test"
Worksheets("input").Range("B5:B10").ClearContents
Worksheets("input").Range("D12").Value = NewRow(1, LBound(NewRow, 2))
Worksheets("input").Range("D13").Value = TransNo
Worksheets("input").Protect Password:="nottherealpasswordwasjustquicker"
Worksheets("cashbook").Protect Password:="nottherealpasswordwasjustquicker"
End Sub
Bookmarks