Updatable Cell where contents is copied from another cell

[TysonHorlacher]

I have several pages of an excel order form I use at work. In each of these pages it has a location for a Shipping Address, PO, contact...

I would like to have all pages capture the Information so my user only has to fill it out 1 time. I would also like to make it updateable from any page in the work book (not solely a single page).

Ex. Customer can change sheet1, C12 and that value will reflect on sheet2, C12 and sheet3, C12. Later when they are on Sheet 3 they can change C12 and it will reflect back on the other 2 sheets with the change.

I assume the easiest way to do this would be to have a macro or function which collects any changes typed in, adjusts a control cell and then displays the contents of the control cell (not what was typed, which if working correctly would match what is typed because that should replace the control cell contents).

Not sure how to write such a function. Any help will be appreciated.

[Olly]

Put this in the ThisWorkbook module. Change the value of sRepRange to suit.

Please Login or Register  to view this content.

[TysonHorlacher]

This worked well but I have 4 other areas to do the same thing. I have tried just using the same code with different ranges to do the same thing for the other cells but now I am getting a compile error. How would I use this code for different ranges in the same workbook?

[Olly]

As I said, just edit this line:

Please Login or Register  to view this content.

Put in the cell addresses you wish to replicate.

For example:

Please Login or Register  to view this content.

[TysonHorlacher]

I figured this out. Instead of calling the worksheet event multiple times, I placed the code within the same event. I then changed the range constant name (1,2,3). I removed the redefining of the constants (as they were already defined as data type). I did have to use Terminate(1,2,3) to get this to work as well but in the end it worked.

[TysonHorlacher]

Here is my code

Please Login or Register  to view this content.

[TysonHorlacher]

Of course, all under workbook change on the top.

[Olly]

Ok - no need to repeat the functionality like that. We can just specify the range to replicate:

Please Login or Register  to view this content.

[TysonHorlacher]

After seeing my code and all my code copying, I am sure there is a better way to do this. If I can leave this cleaner and still functional, that would be best.

Thank you for your help as I am completely self taught in this project.

[TysonHorlacher]

Thanks, my system didn't load your response.

[Olly]

Of course, all under workbook change on the top.

Make sure you're using the Workbook_SheetChange event.

After seeing my code and all my code copying, I am sure there is a better way to do this. If I can leave this cleaner and still functional, that would be best.

Thank you for your help as I am completely self taught in this project.

Revised code already posted.

[TysonHorlacher]

Thanks, my system didn't load your response.