copy same as above data

3/14/2017

Ryan Chong

3/10/2017

try add:

Cells(Selection.Row, "A") = ""
  Cells(Selection.Row, "S") = ""

after:

Rows(Selection.Row - 1).Copy
  Rows(Selection.Row).Insert Shift:=xlDown

Subodh Tiwari (Neeraj)

3/10/2017

You may try something like this....

Sub New_Delta()
    Dim r As Long
    ' Go to last cell
    r = Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
    
    ' Copy formula from cell above
    Range(Cells(r - 1, "B"), Cells(r - 1, "R")).Copy
    
    Range("B" & r).Insert shift:=xlDown
End Sub

3/10/2017

Hi,

Another approach would be :

Sub InsertCopyRow()
Dim Rng As Range
Set Rng = Range("B12").EntireColumn.Find("*", SearchOrder:=xlRows, SearchDirection:=xlPrevious)
If Not Rng Is Nothing Then Application.Goto Rng
    Rng.Offset(1, 0).EntireRow.Insert
    Rng.EntireRow.Copy Rng.Offset(1, 0).EntireRow
End Sub

3/10/2017

ASKER

Hi All,

Thanks for looking into this. As of now Shums' suggestion works great., however the reason I wanted to only copy from B to R is because there is formula in column "S" that I did not want to alter. For some reason the when using Shum's code the formula on column S is shifting down one row. Any thoughts?

Thanks!

3/10/2017

Hi Braulio,

I did included Column S formula to shift down, so you don't need to add manually.

3/10/2017

You can delete all the manual formula which you currently have down the row. My code will pull down the formula from above row.

3/10/2017

ASKER

My Apologies,

I meant to say that Subodh Tiwari (Neeraj)'s suggestion is what that I am using. Shums when I use your suggestion I get this message

3/10/2017

My Macro name was not New_Delta
My code is below:

Sub InsertCopyRow()
Dim Rng As Range
Set Rng = Range("B12").EntireColumn.Find("*", SearchOrder:=xlRows, SearchDirection:=xlPrevious)
If Not Rng Is Nothing Then Application.Goto Rng
    Rng.Offset(1, 0).EntireRow.Insert
    Rng.EntireRow.Copy Rng.Offset(1, 0).EntireRow
End Sub

3/10/2017

ASKER

Shums,

Thanks almost there but your code is including column Columns "S" .

3/10/2017

Please try attached. I deleted formula in S, when you click same as above, it will insert new row and copy down with formula.
Sample-Request-Form_v1.xlsm

3/10/2017

ASKER

Hi Shums,

See image below.. It's on an image do I can illustrate the red 2

3/10/2017

OK, I got it, it was bringing the wrong formula down.

Please try attached...
Sample-Request-Form_v2.xlsm

3/10/2017

ASKER

it is still overlapping the formula... if you look at column S starting at S14 formula shows +2, S15 +3 S16 +3 and so on...

3/10/2017

Neeraj's Code is working perfect, just use his code and credit him

Sub New_Delta()
    Dim r As Long
    ' Go to last cell
    r = Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
    
    ' Copy formula from cell above
    Range(Cells(r - 1, "B"), Cells(r - 1, "R")).Copy
    
    Range("B" & r).Insert shift:=xlDown
    Application.CutCopyMode = False
    
End Sub

3/14/2017

ASKER

All,

Just following up here... I am still having issues with this code. While Nerraj's is working great I need the function to be copy and paste instead of insert. If we use insert my formulated column "S" gets shifted. Is there a way to accomplish this using copy and paste with code?

Thanks in advance for looking into this. File attached
Sample-Request-Form.xlsm

ASKER CERTIFIED SOLUTION

3/14/2017

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

3/14/2017

ASKER

Thank you Shums!!!

This worked like a charm.