Add row with overtime qty

[AlexMurray01]

Hi I'm messing around with vba macros for the first time, a bit lost.

For every entry of Qty > 8, I need to create a new identical row below with Overtime replacing the name, reduce the Qty to 8, and have the remaining Qty in the new row.

Any assistance or advice would be appreciated, let me know if you need more information.

Thanks.

 

[Monty]

Hello Alex

Sub AdjustQty()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long
    
    ' Set the worksheet (change "Sheet1" to your actual sheet name)
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Find the last row with data in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through the data starting from the bottom to avoid issues with inserting rows
    For i = lastRow To 2 Step -1
        If ws.Cells(i, 2).Value > 8 Then
            ' Insert a new row below the current row
            ws.Rows(i + 1).Insert
            
            ' Copy values from the current row to the new row
            ws.Cells(i + 1, 1).Value = ws.Cells(i, 1).Value
            ws.Cells(i + 1, 2).Value = "Overtime"
            ws.Cells(i + 1, 3).Value = ws.Cells(i, 2).Value - 8
            
            ' Update the current row's Qty to 8
            ws.Cells(i, 2).Value = 8
        End If
    Next i
End Sub

This code assumes that your data is in columns A, B, and C. Adjust the sheet name and column references as needed. To run this code, press `Alt + F8` in Excel, select "AdjustQty," and click "Run." Make sure to save your workbook before running any VBA code.

Ler me know!Cheers