Inserting New Row in Middle of Two Worksheets - DIFFERENT ROW NUMBERS

[rjbinney]

Thanks to everyone's awesome help schooling me in VBA today, I solved two very different problems in my worksheet. Trouble is, each solution only works in a vacuum; if both conditions exist, the macros I've created become chaotic.

So back to the drawing board.

These Conditions are "Musts" and Cannot Be Changed

1. I have two worksheets - WB! and RAM!.
2. On WB!, the user lists names in column A.
3. The same names appear in RAM!
   RAM! automatically populates column A with =WB! A
4. Users enter WB-type data and RAM-type data, listed in rows for each name
5. There are times when users need more than one row of data (in columns B through H) for each name on WB!
   (Which means there will be blank cells in WB! A)
6. There are never times when users need more than one row of data for each name on RAM!
7. There are times when users need to enter new names BETWEEN existing names.

The workbook opens with A2:A102 on WB! formatted and ready to go, and A2:A102 on RAM! copying the data from WB! as described in condition 3 above.

To accomplish 5, I wrote a macro that simply inserts a row on WB!. This essentially "splits" the relationship between WB! and RAM!, so you may have started with

WB!:
​

A2 = Project Started
A3 = Staff Hired
A4 = Project Finished
​

and

RAM!:​

A2 = Project Started
A3 = Staff Hired
A4 = Project Finished
​

but then if you need to add more data to, say, "Staff Hired", you'd run the macro and then

WB! becomes:​

A2 = Project Started
A3 = Staff Hired
A4=
A5 = Project Finished
​

and

RAM! remains:​

A2 = Project Started
A3 = Staff Hired
A4 = Project Finished
​

So far, so good.

But I may need to add "Staff Trained" BETWEEN "Staff Hired" and "Project Finished". The macro I wrote selects both sheets and adds a new row in the appropriate spot, but that gets all screwed up once I have more rows on WB! than I do on RAM!

What I would need would be:

• On WB!, add a new row between 4 and 5
• On RAM!, add a new row between 3 and 4

So I can have:

WB!:​

A2 = Project Started
A3 = Staff Hired
A4 =
A5 = Staff Trained
A6 = Project Finished
​

and

RAM!:​

A2 = Project Started
A3 = Staff Hired
A4 = Staff Trained
A5 = Project Finished

​

The only solution I can think of, works like this:

• Select the row in WB! above which the user would like to add a new name (Let's call that "An")
• Insert the new row
• Go to RAM!
• Find the row that corresponds to "An", and insert a row above that.

So in my scenario above, the user would select WB!A5 ("Project Finished") and click the macro button.
Excel would insert a new row WB!A5, pushing all existing data down a row. (This would also mean the data in RAM!A5 now = WB!A6, and so on)
Excel would go to RAM! and look for "Project Finished" and make that row active.
Excel would insert a new row above "Project Finished", and copy the text from the 'new' WB!A5 into that cell.



So here are my questions:
A) Is that logic in the four lines above sound?
B) If "yes", how in the wide world of sports do I write a macro for that????!??



Thanks in advance for your patience and help.

 

[rjbinney]

I believe if I can get the Cells.Find function to take the "What" out of the clipboard, I'd be OK.

Because I can add the row, move down one

ActiveCell.Offset(1, 0).Range("A1").Select​

Copy what's in that cell

Selection.Copy​

Move to RAM!

Sheets("RAM").Select

​

But how to "find" what I just copied out of that offset cell?