Macro - Inserting rows relative to where clicked

[cooper1308]

Hello,

I have verrrry limited knowledge but a basic grasp of VBA/Macros

Currently I am using a standard button (I think, I just copy/paste/re-assigned existing macros) to activate the macro... This macro inserts rows then copy/pastes data from various cells/worksheets....

My problem arises as I need this same macro, multiple times on the same sheet. I need the rows that are inserted to be relative to the row in which the macro was clicked..... But being an image or whatever, the macro can be anywhere on the sheet and it will reference only that original row that I clicked on when recording the macro....and insert rows there. I need the macro to insert the rows immediatley below the row it was clicked...

If someone can explain it to me in laymans terms that'd be great!

Thanks

[AndySuk]

Hi Cooper

The trouble with recording macros is that the VBA is written with absolute references...so if you're first step is to select cell A6 and then insert a row below it then the VBA will look something like;

Please Login or Register  to view this content.

Having said that recording macros is a good way to find out how to do things programatically but the key is to then be able to manipulate the recording;

When you click on a cell it becomes the "active" cell. So the macro needs to take the active cell, go down (offset) one row and then insert a row. The next bit of code does the same thing but does it relative to the cell you clicked on...

Please Login or Register  to view this content.

All you need to do now is to copy the code above into a sub-routine and assign it to your button.

A

[cooper1308]

Hi Cooper

The trouble with recording macros is that the VBA is written with absolute references...so if you're first step is to select cell A6 and then insert a row below it then the VBA will look something like;

Please Login or Register  to view this content.

Having said that recording macros is a good way to find out how to do things programatically but the key is to then be able to manipulate the recording;

When you click on a cell it becomes the "active" cell. So the macro needs to take the active cell, go down (offset) one row and then insert a row. The next bit of code does the same thing but does it relative to the cell you clicked on...

Please Login or Register  to view this content.

All you need to do now is to copy the code above into a sub-routine and assign it to your button.

A

Ahhh I think I got you...

Will test it out tomorrow when I'm back at work..

It's funny, i've managed a couple of crafty moves with macro's I didn't think were initially able to been done... Just about working your way around them!

thanks for the help

[cooper1308]

Hi Cooper

The trouble with recording macros is that the VBA is written with absolute references...so if you're first step is to select cell A6 and then insert a row below it then the VBA will look something like;

Please Login or Register  to view this content.

Having said that recording macros is a good way to find out how to do things programatically but the key is to then be able to manipulate the recording;

When you click on a cell it becomes the "active" cell. So the macro needs to take the active cell, go down (offset) one row and then insert a row. The next bit of code does the same thing but does it relative to the cell you clicked on...

Please Login or Register  to view this content.

All you need to do now is to copy the code above into a sub-routine and assign it to your button.

A

Ok got it working, thanks alot....quickly gaining a better understanding...

Next question, if anyone can help it would be great.

Once the row (I actually insert 3 rows) are inserted The cell automatically selected is the first cell in the new rows...

I needed to copy and paste certain data from the above cells, into exactly the same position on the next worksheet.....without copying this data from an absolute reference (e.g, select 2 cells above, 4 rows across, paste in exact position on next worksheet, return to original worksheet, then copy more data relative to where the last one was copied, and paste in exactly the same on next worksheet)

Anyone know if this can be done?

One last thing, Can I manipulate the button after the macro has been actioned. e.g: Once all the data is cut/paste, It then deletes the button that was clicked (or would that delete all buttons using that macro?).....Ideally once this macro has been pushed the button dissapears then a new one with different macro value is inserted relative to where the original new rows were inserted