Repeating data across worksheets

[JimDandy]

I’d like to create a series of worksheets where the data from the top 5 rows is repeated from sheet to sheet, but that from any sheet within the workbook where this data appears, I’d like to be able to modify it and have that modification reflected on all the other sheets, including the original one where the data was entered.

I’ve only been able to figure out how to enter the data on Sheet1, and have it repeated on Sheet2, Sheet3, etc using a reference to the original data on Sheet1. If I update any of the repeated data on the other worksheets, it only updates the data on that worksheet since I’m only overwriting the reference to the original data from Sheet1.

[Chef BurnsALot]

Click the sheet tab on the first sheet you want to change, then while holding the Shift key, click the last sheet you want to change. Then make any changes, additions, etc. Just be sure to click another sheet to de-select the sheets when finished (or right-click on the sheet tab and click "ungroup").

[JimDandy]

Is there any way to automate that? It's good that I know it, but I doubt that I can get others that will receive this spreadsheet that they need to do this.

[ChemistB]

This code will do the following;
move the new value to sheet1 and
change the formula in the current sheet back to =Sheet1!$C$5 (or whatever cell you're in)

This code needs to be in every worksheet module except for sheet1.

Please Login or Register  to view this content.

Does that work for you? It only works if people are manually entering data (i.e. 1 cell at a time).

[JimDandy]

Yes, that worked! I had originally tried it using a named range of non-sequential cells and some of which were merged, but using cell references instead of a range name worked fine.