Using VB code to copy formulae

Solved/Closed
Report
Posts
11
Registration date
Monday August 9, 2010
Status
Member
Last seen
October 27, 2010
-
Posts
7099
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
January 20, 2022
-
Hi

I am using the following code to insert 'x' number of new rows dependant on value 'y' and it does this just how I want, however, I also want the formulae and conditional formating from the original row to be present in all of the new rows because at the moment only the regular formatting is copied.

Any help is greatly appreciated.



Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lst As Long
Dim Rw As Long

' limit to column D
If Intersect(Target, Columns("D")) Is Nothing Then Exit Sub

Application.EnableEvents = False
On Error Resume Next

Lst = Range("D" & Rows.Count).End(xlUp).Row
'For Rw = Lst To 1 Step -1
' With Range("D" & Rw)
With Range("D" & Lst)
.Offset(1).EntireRow.Resize(.Value - 1).Insert
.Offset(1, 1).Resize(.Value - 1) = .Offset(, 1).Value
End With
'Next Rw

On Error GoTo 0
Application.EnableEvents = True

End Sub


1 reply

Posts
11
Registration date
Monday August 9, 2010
Status
Member
Last seen
October 27, 2010
1
solved it.....
added the following line to the code;

.entirerow.copy .Offset(1).entirerow.Resize(.Value-1)
0
Posts
7099
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
January 20, 2022
490
Hi,

Thanks for having shared your solution with us.

Best regards :)
0