VBA Code for auto updating information on a sheet with information from another sheet

Hi Guys,

Not sure if this is possible but in my attached sheet I have 1 sheet (sheet 1 "fruit") of customers buying fruit, 1 sheet (Sheet 2 "Veg") of customers buying veg, and a summary sheet of them both (Sheet 3) In an attempt to save time & prevent double inputting I was looking for some code that would allow me update the third sheet automatically when first or second sheet is up dated. So basically the code I need in English is

Please Login or Register  to view this content.

Hope you can make sense of it.
Thanks in advance
Attachment 437355Book1.xls

for sheets Fruit and veg code in Sheet_Change


Kind regards
Leo

Hi Leo,

I messed up. I thought if I gave you a simple version I wold be able to amend it to suit my sheet. It is actually a lot more complicated to read than I expected. I have attached an amended sheet. This example uses the exact columns I need changed and also sheet 2 & 3 are both updated from sheet 1, (my mistake earlier saying sheet 3 was updated from sheet 1 & 2.
eg. Goofy bought both fruit & Veg. So as soon as I put Money in sheet 1 it automatically updates sheet 2 & 3 but as Daisy only bought Fruit it only automatically updates sheet 2.
Ps for the record what I incorrectly asked for, your answer done it exactly.

Thanks again and apologies for mix up
Book2.xls

how goes the update now ?
formulas ? other code ?

hard to make second part without vieuw of the first part

Kind regards
Leo

Hi Leo,

Not really sure I understand your question. This is the code I currently have in my sheet (equivalent to the fruit/veg sheet)

Please Login or Register  to view this content.

This allows the information transfer to sheet 2 & 3. I'd like now to be able to update that information afterwards

so this one only updates after payment is done

Kind regards
Leo

Hi Leo

I have made the following changes and not sure I'm right

Please Login or Register  to view this content.

The first red ones are 28 which is the column in which the trigger word "block" is
I renamed sheet two to the actual name Banc ***
& Issued is the word I want put into J Sheet 2.
Same changes on sheet 3
Error is "Cant execute code in break mode". Error happens when I put the date in Sheet 2 column X with Sheets("Banc ***").Range("J" & lr) = "Issued" This seems to be the line causing the error highlighted in yellow

Matt

I have just used your exact formula in the test sheet attached and I am getting the below in red is coming up as a runtime error 9 Subscript out of range

Everything in Blue would be already there until I put a price & date in Sheet 1.

Please Login or Register  to view this content.

Thanks a mil for your patience
Book2.xls

excel file with the real sheet names and real values in columns would help to help you

Kind regards
Leo

Try this, extra code for update after payment

Please Login or Register  to view this content.

Kind regards
Leo

Hi Leo,

Think we are 99% of the way there. Works perfect with Mickey,Goofy, Pluto who have both life insurance & Home Insurance
However if I pick Minnie who has no life insurance I get a run error 1004 "Application-Defined or object-defined error
and if I pick Donald who has no house insurance the sheet 2 works correctly but on sheet 3 it transfers the information to the next available blank line as there is no Donald on that one. Is there a way of building into the code only transfer to sheet to if sheet 1 column AB says block and only transfer to sheet 3 if sheet 1 column AC says block.

Really appreciate your help.

Time is up for now, so cant test but maybe

change this

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

2 times ( also for range AC )

otherwise i see later this day


Kind regards
Leo

Leo,

Thank you so much. This seems to have done the trick. Thanks so much for your patience
Matt