Insert rows based on data from another worksheet

safato7470 · Jul 11th 2020

Iam trying to insert rows based on number of entries in another worksheet, and this code doesn't seem to work. Can anyone help me out here. Thanks in advance.

Code

Sub Insert_S2NUS()


Dim Count As Integer
Count = WorksheetFunction.CountA(Worksheets("Sheet1").Range("W:W")) - 1
Dim numrow
numrow = Count - 500




If Count >= 500 Then


Worksheets("Sheet3").Range("a10").Activate


If IsNumeric(numrow) Then


For I = 1 To numrow


ActiveCell.EntireRow.Select
ActiveCell.EntireRow.Insert Shift:=x1Down
Selection.FillDown


Next I


End If


End If


End Sub

Display More

**royUK** · Jul 11th 2020

Welcome to Ozgrid. Please take the time to read our Forum Rules to understand how to use the Forum correctly. I have added Code Tags this time.

**royUK** · Jul 11th 2020

Try this

Code

Sub Insert_S2NUS()
Dim Count As Integer
Count = WorksheetFunction.CountA(Worksheets("Sheet1").Range("W:W")) - 1
Dim numrow
numrow = Count - 500
If Count >= 500 Then




Worksheets("Sheet3").Rows("10:" & numrow).Insert Shift:=xlDown, _
CopyOrigin:=xlFormatFromLeftOrAbove
End If




End Sub

Display More

Insert rows based on data from another worksheet

Welcome to Ozgrid. Please take the time to read our Forum Rules to understand how to use the Forum correctly. I have added Code Tags this time.

Participate now!

Share

Tags