One line of Data duplicated into multiple lines changing values of some lines

Hi all,

Struggling with this one, even how to describe it.

I have a raw data sheet that has lines of data, for this example I have only used 1 "R order number" (Column E) but in reality there could be 50 to 100. Each R Order Number could have several lines in the Raw Data sheet, and the count is based upon "R Line Number" (Column Y). This example has 1,2 and 3.

On each of those three lines will have subtle differences in several columns which would cause the requirement for a copy of a line to be pasted into the extract sheet and edit Column AM with a new value. These rules are extensive and are included in the workbook. So the three rows can become 10 rows in the extract. The next set of data is appended to the bottom. of the extract list.

I am at a complete loss. The VBA I have created is creating duplicate from RAW Data to the extract sheet, but then I start struggling with amendment of column AM.

So for instance

First rule is if Column AM has a value of "BR", then paste two identical rows in the extract sheet, and in one row put "B" in column AM, and in the other row, put "R" in column AM.

So if each of my RAW Data rows 1,2 & 3 have BR in AM, then I will need 6 rows.

Private Sub ConunandButton1_C1ick()
a = Worksheets("Raw Data").Ce11s(Rows.Count, 1).End(xlUp).Row 'a counts last row in sheet

For i = 2 To a  'from row 2 to a(last row in sheet1)

If Worksheets("Raw Data").Cells(i, 39).Value = "BR" Then  '(if column 39 equals the criteria - amend column number to assess)

    Worksheets("Raw Data").Rows(i).Copy
    Worksheets("Extract").Activate
    b = Worksheets("Extract").Cells(Rows.Count, 1).End(xlüp).Row
    Worksheets("Extract").Cells(b + i, i).Select
    ActiveSheet.Paste
    
    Worksheets("Extract").Cells(b + i, i).Select
    ActiveSheet.Paste
   
    

    
End If

Next

Application.CutCopyMode = False

Sorry all, I am just struggling with this one. The rules have been handed to me so trying to do my best to explain. Any Help is appreciated.