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

Roj47

Board Regular
Joined
May 4, 2011
Messages
54
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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Roj47

Board Regular
Joined
May 4, 2011
Messages
54
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,994
Messages
5,639,436
Members
417,089
Latest member
jonstr101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top