SOLVED! copying rows and inserting them while keeping formulas/formatting by vba

Hello,
I have this code but it is not working for me and I am wondering if you can help.

I want to copy a particular row (2 rows) and insert it in any place I want still keeping the formulas and formatting by the click of a command button. I have the creation of the command button down but the code is giving me a headache. Please can you see where I am wrong and correct it for me please?

Please Login or Register  to view this content.

Thanks

Maybe try something like this :

Please Login or Register  to view this content.

2020-06-17_03-30-07.gif

The code assumes that your row 2 (which has formula and formatting) you want to copy is from column A to C.
Then it ask you to select the range of rows in column A.
If for example you want to insert it below cell A3 then you start to drag from cell A4 down to any row number.

Originally Posted by Fredvonny

Just a thought. is there a way to just use a number in the input box to insert the rows?

Yes .

Please Login or Register  to view this content.

Please remember the answer to "how many rows ?" should be an even number.
Also, I assume that what you want is the inserting cells, not inserting entire row.
For inserting entire row, it would be different code.

thank you so much

You're welcome, Fredvonny.
Glad I can help.

Originally Posted by Fredvonny

the code to copy the entire row and insert?

I limit from column A to Z row 1 and 2 as the range to be copied.
The sub below will insert the entire row as many as the answer for "how many rows ?"

Please Login or Register  to view this content.

Just limit it according to your last column data.
If for example the last column of your data is column ABC,
then change the Z2 to ABC2 in the line Range("A1:Z2").Copy

Hi Fredvonny,
I think it would be better if you can attach a sample workbook,
so we can have a look on how is the data of your situation in the sheet.

BTW, I've just found out that if copying the entire row 1 and row 2,
the code can be shorter :

Please Login or Register  to view this content.

Still need to see how is your data though,
so we can have a look what cause it doesn't do the copy/paste, only the insert.

Fredvonny,

I've just looked into your sample workbook.
I'm very sorry, the code I gave you is not meant to work with that kind of situation like in your sample workbook .

First, I don't quite understand what do you mean that you only want to copy the row 1 and row 2,
since in row 1 and row 2 on sheet "Form" of your sample workbook only contains information and no formula in there.
As seen in the image below :
EXCEL_2020-06-17_23-04-35.png

Second, your sheet has a merge cell,
this sometimes can get unpredictable result when run a macro.
I've read some articles I've found in the internet who advice to avoid use a merge cell as much as possible.
For example in this article.

The image below is the result after I run the macro in your sample workbook.
I need to "carefully" choose the start of the row number (I put number 12) which must be the the first row number of the merged cell.
EXCEL_2020-06-17_23-14-48.png

The above image, the orange and green highlighted is the result after I put 12 as the row number to start the insertion, and put 4 as for the answer of "how many rows?"
.
.
.
.
.
If I just put the start of the row number "carelessly" (I put number 9) which turn out it's the second row number of the merged cell,
the insert process work, but the copy/paste process doesn't work ... as seen in the image below.
EXCEL_2020-06-17_23-20-22.png

As you can see. because I put number 9 to start and row 9 is the second row of the two merged rows (column A), and put 4 as the answer of "how many rows?",
the code did insert four rows (seen in purple color), and the merged cell now expand (the grey color), and the code unable to paste to the insertion area.


Still, I don't understand how do you want the result.
If you have time... please make a new sheet, and by doing it manually,
put the kind of result that you expected after you copy row 1 and row 2,
then insert for three times in any row below it for example.

Anyway, I try to guess what you want....
Assumes that the beginning condition (before running the macro) of sheet Form is like this :
EXCEL_2020-06-17_23-36-53.png



Then after running the macro, you want the result like the below image if you input 2 as the answer of "how many rows?"
EXCEL_2020-06-17_23-38-26.png


So, before running the macro, the last row of the Form is row 15.
After running the macro, the last row of the Form now is row 17.

If that's what you mean, then the code is like this :

Please Login or Register  to view this content.

For the code "know" what is the last row number (not including the row number for total calculation, control total, etc) of the Form,
it find first the word "Total" in column J, because one row above the cell with value "Total" is assumed will always be the last row number of the Form.
So please be sure that there will be only one word "Total" in one cell of the whole column J.

So, in the "beginning" state of the form, the code find that the last two number of the row is row 14 and 15.
That two rows (row 14 and 15) are copied then inserted right below row 15.

That's just my guess on what you want .

WOW!!! WOW!!! WOW!!!! Are you a mind reader??? This is exactly what I want! sorry for confusing you with the first two rows. I was thinking about the first two rows where the data will be filled and you figured it out. I am soooooo grateful! Thank you thank you thank you.

A very grateful Friend
Fredvonny

Code Tags Added
Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)