Add a Blank Row for New Value added to a List

[nlowery12]

I have a List on Names on Sheet A, and the Same list (=Sheet A! A1) on Sheet B. In Sheet B and several other sheets in the workbook there are corresponding values on the same row in every sheet based on the list in Sheet A. The list is in Alphabetical order and I want to add a name to the list and in every other sheet have a blank row added with the new name. For Example Sheet A, A1 = Doe, Jane, A2 = Doe, John. I want to add a Insert a Cell between the two and make it, A1 = Doe, Jane, A2 = Doe, Jim, A3 = Doe, John. When the new name is inserted, I need Sheet B, A2 = Doe, Jim, but I need B2:2 to be Blank and the values that were in the same rows with Doe, Jane and Doe, John to remain the Same.

 

[Snakehips]

@nlowery12
Does it help if you substitute formula in B =SheetA! A1 with =INDEX(Sheet1!A:A,ROWS(A$1:A1),1)
Then drag down and across as required.

 

[nlowery12]

The Index Formula Solves the problem of names not getting automatically added, which I have went around that by using OFFSET on sheet A (I add in column D, the Offset is in column A in one on the pictures below), Is Index a better way of doing this (Faster or Easier) as this is a large workbook and lots of constantly changing names and numbers?

If I drag that across it removes the numbers on the sheet. I have 8 sheets like the one pictured below, all with different numbers on them, and a lot more names than this example. I need the The Blank Row to be added in between the two and move all corresponding numbers with the names they were associated with. I am trying to avoid manually inserting a row to multiple sheets every time I add a name.

 

[Snakehips]

If you are using OFFSET function of a large dataset then you are compromising worksheet performance. Offset is a 'Volotile' function and triggers a recalculation every time there is a change of data within the worksheet. It's your call whether it's use is acceptable in your situation.
I get the fact that you are inserting a new name / blank row in sheet A.
Doe it help to select the range of 8 or whatever tabs, using **** Enter and then Insert Row and Enter Name?
All sheets will then have inseted row and name, for the price of one.
Otherwise, for me, the images you are posting are not really giving much useful information.

 

[nlowery12]

Selecting Multiple Tabs Does Solve the Issue, however I'm trying to find a way for it to automatically happen when added to the List of names on Sheet A, in order to make it more user friendly for someone who doesn't use excel much. I looked for similar VBA codes, but only found ones that would insert into every worksheet, but not select ones.

 

[Snakehips]

Maybe this approach?
Password Protect the various sheets but, tick all boxes bar 'Insert Rows' and 'Delete Rows'. So user can do all but Insert / delete rows.
Then in Sheet"A", have a button to " Insert Name". and have it coded to have user enter a new name into an input box. Then insert name in a new row in each of the applicable sheets.