Macro to copy and paste row

[roniaelm]

Hi,

I am trying to create a macro that will copy the row (from B:Q) my
active cell is in and paste the content into a newly inserted row
below my active cell. Also, I would like it to copy the formulas that
are in the row and paste them as formulas in the new row. Except for
columns F and G I would prefer the formulas are pasted as values
instead.Is this possible?

I would appreciate your help as my knowledge in VB is very basic.

Thanks!

 

[Gary''s Student]

This first inserts the new blank row below the ActiveCell and yjrm does the
pastes

Sub Macro1()
ActiveCell.Offset(1, 0).EntireRow.Insert
n = ActiveCell.Row
Set r1 = Range("B" & n & ":Q" & n)
Set r2 = Range("B" & n + 1)
r1.Copy r2

Set r3 = Range("F" & n & ":G" & n)
Set r4 = Range("F" & n + 1)
r3.Copy
r4.PasteSpecial Paste:=xlPasteValues
End Sub

 

[Don Guillett]

One way

Sub copyrowdown()
mr = ActiveCell.Row
With Cells(ActiveCell.Row, "b")
.Resize(1, 16).Copy
.Resize(1).Insert shift:=xlDown
.Offset(, 4).Resize(1, 2).Value = _
.Offset(, 4).Resize(1, 2).Value
End With
Application.CutCopyMode = False
End Sub

 

[Don Guillett]

or change the second resize to get value from ABOVE.
.Offset(, 4).Resize(1, 2).Value = _
.Offset(, 4).Resize(0, 2).Value

 

[roniaelm]

or change the second resize to get value from ABOVE.
  .Offset(, 4).Resize(1, 2).Value = _
  .Offset(, 4).Resize(0, 2).Value

Thanks the macros worked! You are lifesavers!
Just one more question if I change the macro that it copies the rows
from columns A:Q, is it possible to have the macro delete any content
it copies from column A if it did copy any?

That would be a nice added functionality!

 

[Don Guillett]

Why copy it and then delete it?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

or change the second resize to get value from ABOVE.
.Offset(, 4).Resize(1, 2).Value = _
.Offset(, 4).Resize(0, 2).Value

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
message

Thanks the macros worked! You are lifesavers!
Just one more question if I change the macro that it copies the rows
from columns A:Q, is it possible to have the macro delete any content
it copies from column A if it did copy any?

That would be a nice added functionality!

 

[roniaelm]

Why copy it and then delete it?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software




Thanks the macros worked! You are lifesavers!
Just one more question if I change the macro that it copies the rows
from columns A:Q, is it possible to have the macro delete any content
it copies from column A if it did copy any?

That would be a nice added functionality!

Hi Don,

You are right!
It is unnecessary to do this fro column A as if the row is inserted it
does not automatically copy any information into column A.
What I really need is that the content in column O is not copied as
the user will need to enter information here manually. Is this
possible?

Thanks again!

 

[Don Guillett]

Again, a little logic. Either let the user overwrite the cell or use this
added line .offset(,13)=""

Sub copyrowdown()
mr = ActiveCell.Row
With Cells(ActiveCell.Row, "b")
.Resize(1, 16).Copy
.Resize(1).Insert shift:=xlDown
.Offset(, 4).Resize(1, 2).Value = _
.Offset(, 4).Resize(1, 2).Value
.Offset(, 13) = ""
End With
Application.CutCopyMode = False
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Why copy it and then delete it?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software




Thanks the macros worked! You are lifesavers!
Just one more question if I change the macro that it copies the rows
from columns A:Q, is it possible to have the macro delete any content
it copies from column A if it did copy any?

That would be a nice added functionality!

Hi Don,

You are right!
It is unnecessary to do this fro column A as if the row is inserted it
does not automatically copy any information into column A.
What I really need is that the content in column O is not copied as
the user will need to enter information here manually. Is this
possible?

Thanks again!

 

[roniaelm]

Again, a little logic. Either let the user overwrite the cell or use this
added line .offset(,13)=""

Sub copyrowdown()
mr = ActiveCell.Row
With Cells(ActiveCell.Row, "b")
  .Resize(1, 16).Copy
  .Resize(1).Insert shift:=xlDown
  .Offset(, 4).Resize(1, 2).Value = _
  .Offset(, 4).Resize(1, 2).Value
  .Offset(, 13) = ""
End With
Application.CutCopyMode = False
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software







Hi Don,

You are right!
It is unnecessary to do this fro column A as if the row is inserted it
does not automatically copy any information into column A.
What I really need is that the content in column O is not copied as
the user will need to enter information here manually. Is this
possible?

Thanks again!

Thanks soo much for your help!