Copy Row and Insert Below Based on Value in Specific Cell.

[Roj47]

Hi all and hope you are well.
I have found the answer in part looking through the forums (often copy form one workbook to another) but I do not have the ability to simplify down to my needs.

I have a spreadsheet columns A to BE populated with formulae (usually Vlookups) as well as user input i.e. quantity.
Column U is the product code.
Columns AN and AO are the quantities.

A product change means that existing data in my spreadsheet will become obsolete, but I need the old data to remain as reference should old orders need revisiting.

I am set to undertake the following actions manually:

Identify codes under column U in the range 470251-470270 inclusive.
Copy and paste the relevant rows 1-by-1 under the existing row in each instance.
In the top row of the two remove contents of AO (current quantity no longer required) and leave AN (historical quantity)
In the bottom row of the two remove contents of AN (Do not wish to double up the historical records).

The sheet is approximately 18k rows and the relevant Product Codes are randomly reported throughout.

Thanking in advance for your assistance and will complete the update feedback added under the readmefirst section.

Roj.

 

[Roj47]

Hi. I have sourced [from here] and modified the below but it doesnt appear to copy the row with the value to the row below and seems to remain in a continual loop.

Please could I have some help or if some further info is required.

Thanks.


Sub Copy_Insert_Row()
Dim c As Range
For Each c In Range("u:u")
If c.Value Like "*90047" Then
c.Offset(1, 0).EntireRow.Insert
End If
Next
Dim Area As Range, LastRow As Long
On Error Resume Next
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, _
LookIn:=xlFormulas).Row
End Sub