Copy-paste a row based on a column range value keeping the formatting and size.

Hi there,

I'am trying for a few days to create a code for my Macro to copy-paste a row above a specific column range value keeping the formatting and merging cells into consideration when copying.
Just an explanation of what I'm trying to achieve:

I have two action lists which by default have two rows to be filled. In case that there may be an item that requires additional action I want to be able to have a button that when actioned to add a new row that copies the formatting and sizing of the last row from the two by default.
Now because I have two lists I cannot use a code that specifies a specific row to be taken into consideration as that will affect the second list.
What I need is to be able to create a code that will consider a column value where if that is value is meet, to always copy-and paste the above row above the column value.

Please see the attached photo to have a better idea, as maybe my explanation is not so great

Excel Macro Copy-Paste.PNG

Please let me know if you can help.

Until now, this is the code that I use and it's working but does not copy the format and size it's just adding a row based on the column range value.

Please Login or Register  to view this content.

Kind regards,
Al

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Which button type did you use, I'm guessing that you perhaps have ActiveX buttons, not Form buttons?

I'm not sure if they can be called in the same way, bear with me while I look into it.

You will need to move the button for the bottom table down as well, otherwise the code will insert between the last 2 rows instead of adding to the bottom.

edit:-

Just had a quick look into what is needed, it would be much simpler if you could replace the activex buttons with form control buttons, I've only done a small amount of research, but it looks as if the activex buttons are less versatile.

Hi Jason,

Thank you, I've tried it and it's working and does copy paste also the formatting.

The only issue is that the button works to add the new row at the bottom list just for the first line added, if I add two, then the button is not anymore underneath the last row so it will not add the new one at the bottom list. The button is not moving with the new row added, is fix.

Is there anything that can be done to ensure that the button always goes down simultaneously one row down to ensure it's adding always the new row to the bottom list?

When I tested the code before posting, the button was moving down as the new rows were inserted.

A couple of checks.

Did you make any changes to the code?

Are the buttons definitely in the correct place when you start? With the top left corner of the button in the empty row and in column X. The code works on a specified number of columns, so if the button is too far right then it could be out of range. If this is the case, then which column are the buttons in now?

edit:-

Maybe I had that setting on already and didn't realise it. I don't work with buttons, etc. very often so it's quite easy to overlook settings like this