Multi-Page Range synchronization

[adotsey]

Hello,
I have multiple sheets in the same workbook with exactly the same information in ranges A2:B211. I would like to make it so that one of those sheets is the "master page", so that if any rows were inserted or changed on this sheet, those changes would be copied over to the other pages, prefferably with the push of a button. Is this possible?
Thanks,
~Adam D.

[Leith Ross]

Hello Adam,

This macro will update the information in columns "A" and "B" of all the worksheets expect for a worksheet named "Master". Copy this code into a Standard VBA Module, add a button to the worksheet, and then assign this macro to it.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[adotsey]

Thank you, Leith!
That's a very powerful tool, and it worked wonderfully. A bit too powerful, though. Would you mind showing me how to trim it down to only update the information to certain sheets, leaving the rest alone?
I'm trying to slowly learn this VBA thing.
Thanks!
~Adam D.

[Leith Ross]

Hello Adam,

Here is how you can select specific sheets. Just add the names of the Worksheets in quotes to the Case Statement in red separating them with commas.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[adotsey]

Wonderful!
That worked exactly like you said, thank you very much!
Is there any way to keep the grouping structure when you copy over, and keep the formatting at the same time? As of right now the formatting is coming over just fine, but the grouping structure isn't changed on the "slave" sheets, and it makes a fine bit of a mess.
Also, would you mind explaining a bit of the script? There are parts that I don't understand how they work..

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Again, thank you very much for your patience and knowledge!
~Adam D.

[adotsey]

bump bump bump

[Leith Ross]

Hello Adam,

Sorry about getting back to you so late. I am not sure what you mean about the grouping structure. Could you give me an example?

I'll break down the code for you. LastRow is found by starting at the bottom of a column, in this case "A". VBA compares the next cell to the previous cell until they are different or the last cell in the range (A1:A65535) has been reached. Since it is starting at the bottom, cell A1 marks the end. The End method of a Range has 4 directions. It can go bottom to top (xlUp), top to bottom (xlDown), right to left (xlToLeft), or left to right (xlToRight).

Please Login or Register  to view this content.

MasterRng is a Range Object that is assigned to the range Worksheets("Master").Range("A2:B" & LastRow). Whenever VBA sees MasterRng, it knows we are referring to the Range("A2:B" & LastRow) on the Worksheet named "Master". It is a programming shortcut. Makes the code easier to read and maintain.

Please Login or Register  to view this content.

The Copy method of range has an Argument named Destination. If the argument is omitted, the data goes onto the clipboard, otherwise it is copied to the range you set destination to.

Sincerely,
Leith Ross