VBA - After new row pasted, make value in particular cell zero

[sdh966]

I have the below code working perfectly but I need value in cell column "N" of the new pasted row(s) to be zero. But I need the value in "N" of the original copied row to retain it's value. below is my current code. You can ignore the comments. I was trying different places.

Sub CopyData()
'Updateby Extendoffice 20160922
    Dim xRow As Long
    Dim VInSertNum As Variant
    xRow = 1
    Application.ScreenUpdating = False
    Do While (Cells(xRow, "A") <> "")
        VInSertNum = Cells(xRow, "J")
        If ((VInSertNum > 1) And IsNumeric(VInSertNum)) Then
           Range(Cells(xRow, "A"), Cells(xRow, "AN")).Copy
       '    Cells(xRow, 14).Value = 0 this did all rows
           Range(Cells(xRow + 1, "A"), Cells(xRow + VInSertNum - 1, "AN")).Select
       '    Cells(xRow, 14).Value = 0
           'this did all rows
           Selection.Insert Shift:=xlDown
        '   Cells(xRow, 14).Value = 0 this did the first row only
           xRow = xRow + VInSertNum - 1
        'Cells(xRow - 1, 14).Value = 0
        End If
'         Cells(xRow - 1, 14).Value = 0
        xRow = xRow + 1
 '        Cells(xRow + 1, 14).Value = 0
    Loop
    'Cells(xRow, 14).Value = 0 this did no rows
    Application.ScreenUpdating = False
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub CopyData()
    Dim xRow As Long
    Dim VInSertNum As Variant
    xRow = 1
    Application.ScreenUpdating = False
    Do While Cells(xRow, "A") <> ""
        VInSertNum = Cells(xRow, "J")
        If ((VInSertNum > 1) And IsNumeric(VInSertNum)) Then
           Rows(xRow).Copy
           Rows(xRow + 1).Resize(VInSertNum - 1).Insert shift:=xlDown
           Cells(xRow + 1, 14).Resize(VInSertNum - 1).Value = 0
           xRow = xRow + VInSertNum - 1
        End If
        xRow = xRow + 1
    Loop
    Application.ScreenUpdating = True
End Sub

 

[sdh966]

Excellent. Thanks so much. I added one more line in bold to zero out column "O" also.

Sub CopyData()
Dim xRow As Long
Dim VInSertNum As Variant
xRow = 1
Application.ScreenUpdating = False
Do While Cells(xRow, "A") <> ""
VInSertNum = Cells(xRow, "J")
If ((VInSertNum > 1) And IsNumeric(VInSertNum)) Then
Rows(xRow).Copy
Rows(xRow + 1).Resize(VInSertNum - 1).Insert shift:=xlDown
Cells(xRow + 1, 14).Resize(VInSertNum - 1).Value = 0
Cells(xRow + 1, 15).Resize(VInSertNum - 1).Value = 0
xRow = xRow + VInSertNum - 1
End If
xRow = xRow + 1
Loop
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Glad to help & thanks for the feedback