Greetings everyone,
I haven't been posting for a while here and that's because most of the coding I made since the last time I posted here was similar to the ones I requested in the past. This one however seems to be a more daunting to me although it looks like a simple exercise.
From the attached workbook in the Sales Reporting worksheet, you will find a report of income. The trickiness of the reporting sheet is the part billing section in column K to which reporter has to enter amount paid every month when new payment is made. What I would like to achieve is to increment all payments for all part billing automatically without the reporter doing the calculation manually of the total payments to date in column R.
The issue is that the reporter has to amend the same row to provide the new payment amount once this has become official. So, as an example, in row 10 let's say on 27/09/2019, a payment of £2000 was made which effectively increments the total amount paid to £3000 and make the outstanding amount to £9000 in cell S10.
What would be the best way to record, that change so the reporter won't have to enter and calculate the total amount manually? Perhaps put it in the column Q.
The coding will probably be the rendition of the following if VBA is to be used:
- Create a module for a Before Save Event
- Check last row
- If Date in First Payment column (M) = Date in Last Payment column (N) Then Copy Value in O (Amount Paid) and Paste in R (Paid so Far).
- If Date in First Payment column (M) <> Date in Last Payment column (N) Then MsgBox "It's a new entry" , Sum up Values in R and O.
Much grateful of your thoughts and input on this even if it's only an indication of what I should do.
Much appreciated.
Bookmarks