Dazzawm
Well-known Member
- Joined
- Jan 24, 2011
- Messages
- 3,787
- Office Version
- 365
- Platform
- Windows
I have a code that basically looks for data in column A on sheet 2, in column AE on sheet 1. When that number is found it copies and inserts the entire row above and changes what is in column A to what's in column B on sheet 2 into the copied and inserted row in AE on sheet 1. I need it do exactly the same thing but copy and insert below instead. I imagine it has something to do with the offsets but don't know what! Thanks.
Code:
Sub FindCopyReplaceAbove()
Application.EnableEvents = False
Application.Calculation = xlCalculationManual = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim rng1 As Range
Dim Dn1 As Range
Dim rng2 As Range
Dim Dn2 As Range
Dim n As Long
With Sheets("Sheet2")
Set rng2 = .Range(.Range("A1"), .Range("A" & Rows.count).End(xlUp))
End With
For Each Dn2 In rng2
With Sheets("Sheet1")
Set rng1 = .Range(.Range("AE2"), .Range("AE" & Rows.count).End(xlUp))
For n = rng1.count + 1 To 2 Step -1
With .Range("AE" & n)
If .Value = Dn2 Then
.EntireRow.Interior.ColorIndex = 35
.EntireRow.Copy
.EntireRow.Insert Shift:=xlUp
.Offset(-1).Resize(, 2).Value = Dn2.Offset(, 1).Resize(, 2).Value
End If
End With
Next n
End With
Next Dn2
Application.EnableEvents = True
Application.Calculation = xlCalculationManual = True
Application.ScreenUpdating = True
End Sub