Record previous cell values on a new sheet

[marcosis]

Hi,

My colleague has a sheet that says a customers name, some other details and then a price for an item they purchased.

Whenever they make a payment, currently she just puts a comment in a cell about how much theyve paid, the date they paid and how they paid, amex, cash etc.

Id like a way for her to put the latest payment method, date, amount in and each time she enters a new value it records those 3 values on another sheet 1 after the other. So she could go to the next sheet and look at the clients payment history.

Any help would be much appreciated. I found the below macro but all it does is overwrite the "saved info" cell each time a new entry is made, with the previous entry.

Please Login or Register  to view this content.

Hope that all makes sense.

Thanks!

[Harribone]

What is the name of the sheet where the 3 bits of data are entered?
Are the cells next to each other (eg A1:C1) or non continuous (eg. A1, B4, C7)?

Easiest approach would be to just have a button or similar that you press once the new information is entered. Then the new data is copied and added to the log.
I always find worksheet change just slows everything down so prefer to avoid. Also if a mistake is made then you will be logging mistakes. Click a button to confirm and save to log allows you to correct mistakes before committing to any log.

[marcosis]

Hi Harribone,

I can put them next to each other no problem, which i assume makes life a little simpler.

A button sounds much better because of the reasons you stated.

One other thing that im not sure how difficult it is to do, each row on the first sheet contains a clients name, when they enter payment details and click the button to log the details, if the data is getting saved one after the other, we cant have the clients on each row because the second log for a client will go on the row of the next client.
So my thinking is, when a new client is entered on a row on sheet one, it will copy to be a new header of a column on Sheet 2 so that when new payment details are logged on the clients row, they will go into the clients column on sheet 2.

Even reading that is a bit confusing lol, so if you need a better translation i can create a spreadsheet that just has data in it as the desired result.

Thanks again

[Harribone]

To be honest the questions about cell locations was me thinking aloud and doesn't matter in reality.

Could do with an example file as you say just to see what your sheet layout would be.

As for output easiest approach would to be log each payment on one table so would be good to include client name on each row. Then you can filter the table by client to see their own history.

Also want to try an easy route so you can be shown how it works, the knowledge may assist you in future developments.

If you share an example though we can take it from there.

[marcosis]

Hey,

You're right, store them one after the other in the same table and ill filter as needed.

See attached sheet. The other thing i need it to do is everytime a figure is entered take it off the money paid and then obviously ill just use a simple formula to calculate money remaining.

Hope this all makes sense.

Thanks again

[marcosis]

I think i may have done it!

I used the below to log the payments, although you do have to select each range, then i used a simple SUMIF to add up the total payment made for each persons entry on sheet 2.

Any adjustments that you think might help, im always open to suggestions!

Please Login or Register  to view this content.

[Harribone]

Yeah that code does seem to do the job. I'm no VBA expert so if there is a way to improve it I couldn't tell you!
Interesting that the ScreenUpdating code is designed to put ScreenUpdating back to the state it was before the code ran, even if it was False. I always thought it was best to set back to True regardless, shouldn't be a problem doing it that way though.

Only thing I would do is sort the layout with gaps and add comments to the code for ease of reading, especially if you need to review at a later date.

Comment can be added to the code like this:

Please Login or Register  to view this content.

I would've built the routine in a way that you need to select the range first and then the code would work on the active selection.
Your approach is better as you could run the code with the incorrect selection my way.