I have some formulas in row 21 & 22 of from column C to N & in corresponding cell of column b is for serial number.
I want to drag down all formulas & data validation rule automatically in row 23 & 24, 25 & 26, 27 & 28 and so on of same column whenever I put next serial number.
How to do it aitomatically.
I already have created macro code for it but unable to apply it. Macro code is below this line. Kindly help.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sourceRow1 As Long: sourceRow1 = 21
Dim sourceRow2 As Long: sourceRow2 = 22
Dim newRow1 As Long, newRow2 As Long
Dim lastCol As Long, col As Long
Dim cell As Range
If Not Intersect(Target, Me.Columns("B")) Is Nothing Then
Application.EnableEvents = False
For Each cell In Intersect(Target, Me.Columns("B"))
If IsNumeric(cell.Value) And cell.Row >= 23 And cell.Row Mod 2 = 1 Then
newRow1 = cell.Row
newRow2 = newRow1 + 1
lastCol = Me.Cells(sourceRow1, Me.Columns.Count).End(xlToLeft).Column
For col = 3 To lastCol
' Copy formulas from row 21 → current
If Me.Cells(sourceRow1, col).HasFormula Then
Me.Cells(newRow1, col).Formula = Me.Cells(sourceRow1, col).Formula
End If
If Me.Cells(sourceRow2, col).HasFormula Then
Me.Cells(newRow2, col).Formula = Me.Cells(sourceRow2, col).Formula
End If
' Copy drop-downs from row 21/22
On Error Resume Next
If Me.Cells(sourceRow1, col).Validation.Type <> -1 Then
With Me.Cells(sourceRow1, col).Validation
Me.Cells(newRow1, col).Validation.Delete
Me.Cells(newRow1, col).Validation.Add Type:=.Type, _
AlertStyle:=.AlertStyle, _
Operator:=.Operator, _
Formula1:=.Formula1, _
Formula2:=.Formula2
End With
End If
If Me.Cells(sourceRow2, col).Validation.Type <> -1 Then
With Me.Cells(sourceRow2, col).Validation
Me.Cells(newRow2, col).Validation.Delete
Me.Cells(newRow2, col).Validation.Add Type:=.Type, _
AlertStyle:=.AlertStyle, _
Operator:=.Operator, _
Formula1:=.Formula1, _
Formula2:=.Formula2
End With
End If
On Error GoTo 0
Next col
End If
Next cell
Application.EnableEvents = True
End If
End Sub
