Macro to cope row above and replace some numbers

[rskn]

Hi all

This is my first time here, and im not very good with scripting, so bear with me

I'm trying to make a macro that copies a selected row and then changes a few values.

My macro so far is this:

Sub New_Revision2()
'
' New_Revision2 Macro
'

'
    ActiveCell.EntireRow.Select
    Selection.Copy
    Selection.Insert Shift:=xlDown
    Range("C" & (ActiveCell.Row)).Select
    ActiveCell.FormulaR1C1 = "=R[-1]C"
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("U" & (ActiveCell.Row)).Select
    ActiveCell.FormulaR1C1 = "=R[-1]C+1"
    Range("V" & (ActiveCell.Row)).Select
    ActiveCell.FormulaR1C1 = "=R3C2"
    Intersect(Range("U:V"), ActiveCell.EntireRow).Select
    ActiveCell.FormulaR1C1 = "=R[1]C"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

This is what happens when i run the macro.
I can copy the selected row, and the "X" is removed. But i cant get the shown data below to be updated correctly.

I hope anyone can help me?

 

[Fluff]

How about

Sub rskn()
   Dim Cl As Range
   
   Set Cl = ActiveCell
   Cl.Offset(1).EntireRow.Insert
   Cl.EntireRow.Resize(2).FillDown
   With Range("C" & Cl.Row)
      .Offset(1).FormulaR1C1 = "=R[-1]C"
      .ClearContents
   End With
   With Range("U" & Cl.Row)
      .Offset(1).Value = .Value + 1
   End With
   Range("V" & Cl.Row).Offset(1).Value = Range("B3").Value
End Sub

 

[rskn]

How about

Sub rskn()
   Dim Cl As Range
  
   Set Cl = ActiveCell
   Cl.Offset(1).EntireRow.Insert
   Cl.EntireRow.Resize(2).FillDown
   With Range("C" & Cl.Row)
      .Offset(1).FormulaR1C1 = "=R[-1]C"
      .ClearContents
   End With
   With Range("U" & Cl.Row)
      .Offset(1).Value = .Value + 1
   End With
   Range("V" & Cl.Row).Offset(1).Value = Range("B3").Value
End Sub

Hi Fluff

Thanks it works somewhat.
1. it makes a 0 instead of a X:

2. I have a reference on another sheet for ex. row 1198. when i run the macro it needs to keep the reference on the other sheet to the new row created by the macro.
I don't know if it makes sense
When i do it manually now i select the row -> Copy -> Select the same row and then "insert copied cells". I guess this inserts the cells above, and by doing that, keeps the reference on the other sheets.

 

[Fluff]

Ok, how about

Sub rskn()
   Dim Cl As Range
   
   Set Cl = ActiveCell
   Cl.EntireRow.Copy
   Cl.EntireRow.Insert
'   Cl.EntireRow.Resize(2).FillDown
   With Range("C" & Cl.Row)
      .Value = .Offset(-1).Value
      .Offset(-1).ClearContents
   End With
   With Range("U" & Cl.Row)
      .Value = .Offset(-1).Value + 1
   End With
   Range("V" & Cl.Row).Value = Range("B3").Value
End Sub

 

[rskn]

Absolutely amazing!
Thanks a million Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.