Macro Insert Row Below Copy Formulas and Formats

[Solon Aquila]

Greetings,

I have a spreadsheet in which we sometimes need to insert a row right in the middle of a bunch of filled-out rows.
I'd like to have the inserted row contain the same formulas and conditional formats as the other rows, but not copy the text/values.

I've tried a few things but can't seem to copy/paste just the formulas/conditional formats.
I've seen a few suggestions (PasteSpecial Paste:xlFormats or something) but just don't know enough to make them work.

here's what I'm using now:

Sub Copy_One_Row_Below()
With Range(Cells(ActiveCell.Row, 2), Cells(ActiveCell.Row, 16))
.Copy
.Insert shift:=xlDown
Application.CutCopyMode = False

End With
Cells(ActiveCell.Row + 1, 2) = "=" & Cells(ActiveCell.Row, 2).Address & "+1"
End Sub

It works as long as I don't mind the data being pasted as well.

Any suggestions?

Thanks!
Solon

 

[John Davis]

Greetings,

I have a spreadsheet in which we sometimes need to insert a row right in the middle of a bunch of filled-out rows.
I'd like to have the inserted row contain the same formulas and conditional formats as the other rows, but not copy the text/values.

I've tried a few things but can't seem to copy/paste just the formulas/conditional formats.
I've seen a few suggestions (PasteSpecial Paste:xlFormats or something) but just don't know enough to make them work.

here's what I'm using now:

Sub Copy_One_Row_Below()
With Range(Cells(ActiveCell.Row, 2), Cells(ActiveCell.Row, 16))
.Copy
.Insert shift:=xlDown
Application.CutCopyMode = False

End With
Cells(ActiveCell.Row + 1, 2) = "=" & Cells(ActiveCell.Row, 2).Address & "+1"
End Sub

It works as long as I don't mind the data being pasted as well.

Any suggestions?

Thanks!
Solon

Untested by maybe:

Sub Copy_One_Row_Below()
With Range(Cells(ActiveCell.Row, 2), Cells(ActiveCell.Row, 16))
.Copy
.Insert shift:=xlDown
.ClearContents
Application.CutCopyMode = False

End With
Cells(ActiveCell.Row + 1, 2) = "=" & Cells(ActiveCell.Row, 2).Address & "+1"
End Sub

 

[Solon Aquila]

John,

Thanks for the quick reply and darn, so close. I got the conditional formats, but not the formulas.

I tried moving the 'clear contents' down to after the With and Excel got mad at me.

Hmm... .PasteSpecial xlPasteFormulas (and xlPasteFormats) gave me TWO rows.

This is an intriguing one.

 

[John Davis]

John,

Thanks for the quick reply and darn, so close. I got the conditional formats, but not the formulas.

I tried moving the 'clear contents' down to after the With and Excel got mad at me.

Hmm... .PasteSpecial xlPasteFormulas (and xlPasteFormats) gave me TWO rows.

This is an intriguing one.

Maybe:

Sub Copy_One_Row_Below()
With Range(Cells(ActiveCell.Row, 2), Cells(ActiveCell.Row, 16))
.Offset(1).Insert shift:=xlDown
.Copy
.Offset(1).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
.Offset(1).PasteSpecial xlPasteFormats
Application.CutCopyMode = False

End With
Cells(ActiveCell.Row + 1, 2) = "=" & Cells(ActiveCell.Row, 2).Address & "+1"
End Sub

 

[Solon Aquila]

I thought I had posted a Thank You to this, but apparently not.

Thanks for your time and help, it's all fixed now!

Solon

 

[John Davis]

You're welcome. Glad to help out, and thanks for the feedback.

 

[fusi0n]

Maybe:

Sub Copy_One_Row_Below()
With Range(Cells(ActiveCell.Row, 2), Cells(ActiveCell.Row, 16))
.Offset(1).Insert shift:=xlDown
.Copy
.Offset(1).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
.Offset(1).PasteSpecial xlPasteFormats
Application.CutCopyMode = False

End With
Cells(ActiveCell.Row + 1, 2) = "=" & Cells(ActiveCell.Row, 2).Address & "+1"
End Sub

Can this be achieved with Worksheet_Change?

 

[uchiha]

Hi Champions
I have one sheet in excel where I need to add rows below the row 12 using a button.
now the macro should work in a way that first click on the button inserts below row 12 copying only the formats and formulae from row 12 to 13.
next click should do the same above step but should insert below 13 and so on.
I am trying to make a Rate Calculator in which I need to have a dynamic sheet where user can add any number of entries they want by clicking this add button

I hope someone will be able to assist me here
Thanks
Imran