Client account Spreadsheet - Compile error - to large to need shorten code

wow... it works nearlty perfect but still one problem.

When it writes to the sheet after you input it needs to be the other way round. When it credits a sheet it actually needs to go into the sheet as a - and vice versa.. becasue the sheets themselves need to balance against the cashbook sheet if you know what i mean. Can we just change from + to - when it writes on the sheet for a property.. then its perfect

Originally Posted by spazem

When it writes to the sheet after you input it needs to be the other way round. When it credits a sheet it actually needs to go into the sheet as a - and vice versa.. becasue the sheets themselves need to balance against the cashbook sheet if you know what i mean. Can we just change from + to - when it writes on the sheet for a property.. then its perfect

I'm still a bit in the dark as to the actual sheet format, but is this any closer?

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