add a new row or column and it automatically adds it to the rest of the sheets

[dragonbabyjenna]

Is there a macro that can be used in an Excel workbook, where you can add a new row or column and it automatically adds it to the rest of the sheets? Please let help me, I will greatly appreciate it!

Have a great day!

[mikerickson]

1) The moderators are going to holler at you about the thread title unless you change it before they see it.

2) The macro you seek is possible. Are you trying: A) if row/column added to Master Sheet then add to all sheets or B) if row/column added to any sheet add to all sheets.

[dragonbabyjenna]

Adding from the master sheet/ the top page of the book please.

Thank you and have a wonderful day!

[mikerickson]

This routine will mirror the inserting and deleting of rows and columns.
If rows or columns are inserted or deleted from the Master Sheet, this routine will insert and delete matching rows/columns on each of the other sheets in the workbook.

To smoothly install this routine:

1) Save a backup file of your workbook in case that idiot from the internet (that would be me) is wrong.
2) Open your workbook and activate a worksheet that is not the Master Sheet.
3) Open the VBEditor and put this in the code module for the Master Sheet.

Please Login or Register  to view this content.

4) Insert a normal code module and put this in that.

Please Login or Register  to view this content.

Every time the Master Sheet is activated, each row and column is given a Name. Named ranges automatically adjust to insertions and deletions. When the sheet is deactivated, the positions of the named ranges show which rows or columns have been inserted or deleted, which drives equivalent insertions/deletions on each of the other sheets.

The whole thing begins with the master sheet being activated, which is the reason for a non-master sheet being active when the code is entered.

The "feature" described, where deleting a column on one sheet automatically deletes a column on another, is very volatile and (IMO) dangerous. When deleting a column on the master sheet, the user risks loosing valuable information on another sheet (that they cannot see) and not knowing that they have deleted that data. Since this is driven by a macro, there is no Un-Do to this data loss. The easiest way to remove the delete feature of this routine is to make

Please Login or Register  to view this content.

the first line of the subs deleteRows and deleteColumns.

I hope this helps.

[Zarcinc]

I want to thank you for this procedure, I have it working on adding and deleting row. Now i if i add data in the master cells how do i populate the child worksheets?

thank you

[mikerickson]

The easiest way to add data to the same cells of multiple sheets is to select multiple sheets (click on sheet tabs while holding CMD or SHIFT on a Mac). Putting data into one of the multiple sheets will add data to the same cell of all the selected sheets.

[Zarcinc]

Yes that is a good shortcut but i am trying to update mutiple worksheets as data changes. I believe the only way the that you can use the mutiple insert short cut is when the tabs are higlighed. Which will not work with this senerio. We are using these to gather data and update inventory. We will have a unique filed in col A of each sheet. Users will update the different sheets. We want to use a master sheet to add and delete inventory.
I like your macro that will add and delete rows when done so from the master sheet. I need to figure out how to copy the unique Col A row informaion that is typed in the master sheet and populate the child sheets. I believe we can do a vlookup once we have the unique id on col A, but trying to figure out where to put it so when data is changed and sheets are changed they are updated

[mikerickson]

I think this might work for you, when put in the ThisWorkbook code module.
Note that ParentAndChildSheet has the parent sheet, the master sheet, as the first item and all the child sheets as the rest.

Please Login or Register  to view this content.