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

rjbinney

Active Member
Joined
Dec 20, 2010
Messages
250
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

rjbinney

Active Member
Joined
Dec 20, 2010
Messages
250
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,909
Messages
5,525,581
Members
409,651
Latest member
Quasar Hunter

This Week's Hot Topics

Top