Mirror cells on four sheets and reusable

[thecdnmole]

I have a workbook with many worksheets and when I enter a checkmark in a cell on sheet 1, I want it duplicated on sheets 2,3 and 4. I have used "=A1", but if that formula is manually removed with a different entry, I need to re-write that formula in the cell so the workbook can be used again. I tried a list of all the cells and doing formula using VBA, but that seemed to take a long time to re-write the code in the cells. I used the below formula in a few places and decided to try it. I like it that it is reusable and I don't have to re-write the links, but it is also very slow writing the checkmark to the other cells on the other 3 sheets. Doe snayone know of a effecient way to do this and be reusable? Thanks for any help.

Please Login or Register  to view this content.

[xladept]

Why isn't this:

Please Login or Register  to view this content.

always 2

[thecdnmole]

Why isn't this:

Please Login or Register  to view this content.

always 2

Sorry, not sure what you mean by "always 2". What should be 2?

[xladept]

Well for "Cells(1,1)" The Column is always 1 and 1+1=2??

[thecdnmole]

Well to be honest I searched the internet for something that would work and found this, tried it and it does! I know little about VBA, slowly learning from trial and error and have about 200 pages of code in this workbook, but I know it's not very efficient, but it is working. As I learn more, I go back and recode the first stuff and hopefully it get's better. But, you have more stumped here, so if you could please provide help, would be appreciated. So, would it work better without the +1?

[thecdnmole]

I removed the +1 and it puts the checkmark in sheet2 one column to the left of where it should be.

[xladept]

I'll take a longer look tomorrow

[thecdnmole]

Thanks xladept! Really appreciate it.

[thecdnmole]

I wonder, would it work, more efficient, faster if I had the formulas off to the side or even on a hidden worksheet and just copy and past them in sheets2-4 using vba instead of using vba to write the formula (sheet1.range("B1").formula = "='L-EX 1_2&4-VP(51)'!R68" for example? Not sure if this is better?

[xladept]

I'm stymied can you either post the whole code or:both?

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[thecdnmole]

I figured you were going to ask, which I know helps greatly, so here is a real simple example. Enter something in cells A1-A6 and it is mirrored in same cells on sheets 2 and 3. Column B1-B6 also has the old standby =A1 reference, but if it is written over, the formula is gone and I need this to work again and again if they want to reuse the same data, except make small changes where needed.

[xladept]

How about:

Please Login or Register  to view this content.

[thecdnmole]

Sweet! That looks more efficient! I don't want/need this for every page in the working project, only 3 pages out of the 49. How would you could it then?

I just tried to delete what I entered and the formula you provided does not do this. I have to go to the other sheets to delete the mirrored values.

One more thing, I can't have this mirror every cell on the worksheet, only select cells. So that would mean I would have to name the range of cells this can only work on. Thanks!

[xladept]

I'd put their names in an array and only access those three. You don't have to name the range but only allow the procedure if the Target is in that range

[thecdnmole]

Can you provide an example? I was thinking this, as below, but that limits data entry, not which sheets are mirrored to.

Please Login or Register  to view this content.

[xladept]

What are the sheet names?

[thecdnmole]

Sheet4 is the main data entry sheet and sheet19, sheet20 and sheet21 are the mirrored sheets.

[xladept]

Try this:

Please Login or Register  to view this content.

[thecdnmole]

Thanks! It works 100% on my test workbook, but when I put it into the real workbook I get "Runtime error 9", subscript out of range! It works though if I do this...

Please Login or Register  to view this content.

any suggestions?

[xladept]

It looks as though Sheet ought to be lower case:

Please Login or Register  to view this content.

[thecdnmole]

Thanks, but that did not work either. Maybe it's because I am putting it in a double-click event??

[xladept]

Well - Can I see the current event code?

[thecdnmole]

Thanks for your help xladept, I will just use my last revision of your code, with a line for each sheet which works just as fine. Much appreciated!

[xladept]

You're welcome and thanks for the rep!