Adding Row Macro

[NoiCe]

Hi,

I currently have a macro for a spreadsheet where it automatically adds a row (after copying a master row that is hidden) above the last line in a spreadsheet, works perfect!

Now, I want to be able to write a macro that will copy that same master row but insert it in a row that is currently the active cell, however once the macro copies the master row that is hidden i assume that it loses what the active row is. Is there a way to do this or possibly set a paramter to ask the user to enter in the row that they want to insert?

Thanks!

-J

[Richard Buttrey]

Hi,

Your question is not quite clear, to me at least.

If your macro inserts a row above the current active cell and copies your master row to this new row, then by definition after the copy the previous active row is now one row below the copied row.

What is your aim with this?

Rgds

[NoiCe]

My apologies...let me see if I can clear this up. This is a checkbook spreadsheet with calculations...

My current macro:
Copies this master row by looking for a special character (which I have set to be the last row titled "Do Not Delete This Row") so it will insert the copied row above this one.

For Example:
A1:D1 is the master row
A4:D4 is the row that states "Do Not Delete This Row" with the special character
Once I run the macro A1:D1 is copied and inserted as a new row above A4:D4 (which now gets pushed down to A5:D5)

New Macro:
Let's say I have information currently written in rows A2:D2 and A3:D3. I realize that I forgot to put an entry in my checkbook and it needs to go between rows A2 and A3. So I want to copy A1:D1 (since this holds the master formula for calculation) and want to insert it between rows A2 and A3. My current macro does not work because it is looking for a special character that lies only in that last row which would not help me out in this situation.

I hope this makes more sense. If not I can upload the spreadsheet. Let me know.

Thanks for your help in advance.

[jaslake]

Hi NoiCe
I'll be glad to look at this for you but I'd like to see what your current procedure is doing and for that I'd need a copy of your workbook. As an alternative, you could continue to use your existing macro and introduce a sort procedure to get the data back in proper sequence (be that date, check number, etc).
Let me know how you wish to proceed.
John

[Richard Buttrey]

Hi,

Am I correct therefore that some of the time the new row is inserted above row 4 and at other times it has to be inserted somewhere else. If so then I'd be inclined to have a Message Box give a prompt to the user, and based on the Yes/No response insert the new row in the appropriate place.

I'd also be inclined to name the range A1:D1 'Master_Formula'

So something like the following

Please Login or Register  to view this content.

HTH

[jaslake]

Hi NoiCe
I support Mr. Buttrey's recommendation to

name the range A1:D1 'Master_Formula'

but I'd be inclined to use a double click event when the user wishes to insert a row to "fix" an issue. The procedure only gets called if they intend to insert a row which is not in the normal course of events. The code would be

Please Login or Register  to view this content.

In my check register worksheet, I use the sort procedure I previously described. Your issues more than likely are different than mine.
If you have issues with this code, please let me know. John

[NoiCe]

Hi,

I uploaded a snippet of my spreadsheet. There are hidden rows and the "Master" row that I mentioned is Row 12. Please let me know if you have any questions.

Thanks,

-J

[jaslake]

Hi NoiCe
I've added a double click event to your register to illustrate my previous point. Double click on cell A of the row you wish to insert (it will insert on the row above the cell you click). The formula in column I is adjusted. I'm color blind so I didn't add colors to the inserted row but you can modify the code easily enough to do that.
Let me know if this helps. John