rjbinney
Active Member
- Joined
- Dec 20, 2010
- Messages
- 279
- Office Version
- 365
- Platform
- 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
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
but then if you need to add more data to, say, "Staff Hired", you'd run the macro and then
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:
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.
So back to the drawing board.
These Conditions are "Musts" and Cannot Be Changed
- I have two worksheets - WB! and RAM!.
- On WB!, the user lists names in column A.
- The same names appear in RAM!
RAM! automatically populates column A with =WB! A - Users enter WB-type data and RAM-type data, listed in rows for each name
- 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) - There are never times when users need more than one row of data for each name on RAM!
- 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
andA3 = Staff Hired
A4 = Project Finished
RAM!:
A2 = Project Started
A3 = Staff Hired
A4 = Project Finished
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
andA3 = Staff Hired
A4=
A5 = Project Finished
RAM! remains:
A2 = Project Started
A3 = Staff Hired
A4 = Project Finished
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
WB!:
A2 = Project Started
A3 = Staff Hired
A4 =
A5 = Staff Trained
A6 = Project Finished
andA3 = Staff Hired
A4 =
A5 = Staff Trained
A6 = Project Finished
RAM!:
A2 = Project Started
A3 = Staff Hired
A4 = Staff Trained
A5 = Project Finished
The only solution I can think of, works like this:A3 = Staff Hired
A4 = Staff Trained
A5 = Project Finished
- 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.