Modify Unique Identifier Code to Place Values in Different Column

Hello all,

I am an absolute noob when it come to VBA and mostly I am running other's code without fully knowing what the lines are doing, so please forgive me if I ask something that isn't possible or seems obvious.
Thank you so much in advance for your help!

Overall picture:

I am working on a macro to:
1. take a series of records from one sheet and copy them to another (currently manually done - will work on this later)
2. Replicate those records into multiple copies of the same information based on the value in column "M"
3. Add a unique identifier to the now duplicated values so that each record is now unique

So far I have step two completed with the following code:

Step 2a)

Sub Add_Records_by_cell_value()

Dim wks As Worksheet
Dim iRow As Long
Dim firstrow As Long
Dim lastrow As Long
Dim HowManyMore As Long

Set wks = Worksheets("Sheet2")
With wks
firstrow = 2 'headers in row 1???
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = lastrow To firstrow Step -1
HowManyMore = .Cells(iRow, "M").Value - 1
If HowManyMore > 0 Then
.Rows(iRow + 1).Resize(HowManyMore).Insert
.Cells(iRow + 1, "A").Resize(HowManyMore, 1).Value _
= .Cells(iRow, "A").Value
.Cells(iRow + 1, "M").Resize(HowManyMore, 1).Value _
= .Cells(iRow, "M").Value
End If
Next iRow

End With
End Sub

Step 2b)

Sub Replicate_data()
' step to populate cells with previous info

    Range("A1:L1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Range("C3").Activate
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Application.CutCopyMode = False
    Selection.FormulaR1C1 = "=R[-1]C"
End Sub

I then found a code (below) to add a unique identifier to the repeated values but I want to have these values placed in column "O" rather than right beside the 1st column ("A"). It would also be nice to maintain the leading zeros from the Lot # but I know that can be tricky and it is not essential.

Step 3)

Sub Numbering2()
    Dim rng As Range
    Dim rngcnt As Range
    Dim firstrow As Long
    Dim lastrow As Long
    Dim columnNumber As Long
    Dim wks As Worksheet

    Set wks = Worksheets("Sheet2") 'change to your sheet
    firstrow = 2 'change to your first row of data
    columnNumber = 1 'change to the column number

    With wks
        lastrow = .Cells(.Rows.Count, columnNumber).End(xlUp).Row
        For i = firstrow To lastrow
            .Cells(i, columnNumber + 1) = .Cells(i, columnNumber) & "_" & Format(Application.WorksheetFunction.CountIf(.Range(.Cells(firstrow, columnNumber), .Cells(i, columnNumber)), .Cells(i, columnNumber)))
        Next i
    End With

End Sub

I have attached an excel book with a sheet showing the result after step 1 and 2 using the above code (1st sheet) and the result after running step 3 code (2nd sheet)

P.S. if anyone has any hints on glaring inefficiencies or repeated/superfluous statements, please don't hesitate to make a suggestion, I really have no idea what I'm doing

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. As you are new I have done it for you this time.)