ExcelBanter - Copying a row and pasting the same below it with just formulas

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Copying a row and pasting the same below it with just formulas (https://www.excelbanter.com/excel-programming/439489-copying-row-pasting-same-below-just-formulas.html)

Copying a row and pasting the same below it with just formulas

Hi all,

I have 3 buttons in the same worksheet to copy a row and insert the
same below it. I have this macro running for 3 different places. See
my code below.

The problem is:

1) I want just the formulas/formatting to be pasted, and not the
constants.
2) And more importantly, if I use Button 1, then a row is added below
row 15 and the rows move down and so for Button 2, row 36 is different
now as it has moved down, if you understand? How do I keep it fixed
that it always copies row 36 even if a row is added above and the rows
move down. Same for Button 3.

Private Sub CommandButton1_Click()
Range("15:15").Select
Selection.Copy
Selection.Insert Shift:=xlDown
End Sub

Private Sub CommandButton2_Click()
Range("36:36").Select
Selection.Copy
Selection.Insert Shift:=xlDown
End Sub

Private Sub CommandButton3_Click()
Range("41:41").Select
Selection.Copy
Selection.Insert Shift:=xlDown
End Sub

Copying a row and pasting the same below it with just formulas

Thanks Don, but my problem is that I want to copy and paste the row
with formulas. So, rows 15, 36 and 41 have formulas.....but when I run
the button for row 15, a new row (which is a copy of row 15) is added
and row 36 becomes 37, but the macro is still using the row 36 and not
changing itself to copy row 37 instead.

I tried naming the cell, but still the same thing happens. I think
because I am copying the row, your code won`t work as it just inserts
a new row.

Thanks,
Shivam

On Feb 12, 5:05*pm, "Don Guillett" wrote:
That is what is happening now. You are inserting at row 36. If you mean you
want to insert where the original row 36 is NOW then name that cell rtm1
(select cell in name box (left of formula box) type in rtm1. Now use this
change and a row will be inserted wherever that cell is

Sub insertatfixedrow()
'Range("36:36").Select
' * *Selection.Copy
' * *Selection.Insert Shift:=xlDown

'use this instead
Range("rtm1").EntireRow.Insert
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
" wrote in message

...

Hi all,

I have 3 buttons in the same worksheet to copy a row and insert the
same below it. I have this macro running for 3 different places. See
my code below.

The problem is:

1) I want just the formulas/formatting to be pasted, and not the
constants.
2) And more importantly, if I use Button 1, then a row is added below
row 15 and the rows move down and so for Button 2, row 36 is different
now as it has moved down, if you understand? How do I keep it fixed
that it always copies row 36 even if a row is added above and the rows
move down. Same for Button 3.

Private Sub CommandButton1_Click()
* *Range("15:15").Select
* *Selection.Copy
* *Selection.Insert Shift:=xlDown
End Sub

Private Sub CommandButton2_Click()
* *Range("36:36").Select
* *Selection.Copy
* *Selection.Insert Shift:=xlDown
End Sub

Private Sub CommandButton3_Click()
* *Range("41:41").Select
* *Selection.Copy
* *Selection.Insert Shift:=xlDown
End Sub

Copying a row and pasting the same below it with just formulas