|
Macro to copy and paste row
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! |
Macro to copy and paste row
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 -- Gary''s Student - gsnu2007k " wrote: 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! |
Macro to copy and paste row
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 Microsoft MVP Excel SalesAid Software wrote in message ... 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! |
Macro to copy and paste row
On Jan 5, 12:49*am, "Don Guillett" wrote:
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 "Don Guillett" wrote in message ... 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 Microsoft MVP Excel SalesAid Software wrote in message .... 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! 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! |
Macro to copy and paste row
|
Macro to copy and paste row
On Jan 6, 1:33*am, "Don Guillett" wrote:
Why copy it and then delete it? -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... On Jan 5, 12:49 am, "Don Guillett" wrote: 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 "Don Guillett" wrote in message ... 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 Microsoft MVP Excel SalesAid Software wrote in message .... 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! 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! |
Macro to copy and paste row
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 wrote in message ... On Jan 6, 1:33 am, "Don Guillett" wrote: Why copy it and then delete it? -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... On Jan 5, 12:49 am, "Don Guillett" wrote: 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 "Don Guillett" wrote in message ... 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 Microsoft MVP Excel SalesAid Software wrote in message ... 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! 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! |
Macro to copy and paste row
On Jan 7, 12:07*am, "Don Guillett" wrote:
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 wrote in message ... On Jan 6, 1:33 am, "Don Guillett" wrote: Why copy it and then delete it? -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... On Jan 5, 12:49 am, "Don Guillett" wrote: 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 "Don Guillett" wrote in message ... 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 Microsoft MVP Excel SalesAid Software wrote in message ... 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! 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! Thanks soo much for your help! |
| All times are GMT +1. The time now is 02:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com