VBA code help

[jwevans]

I am running the following VBA code to transfer data from Worksheet Active Bays to worksheet Work Order Log. The code will not transfer data to the first 4 rows on the Work Order Log - it does delete the data from the Active Bays worksheet. If there is already data in a row below the first 4 rows on the Work Order Log worksheet then the data will tranfer correctly.

Sub SaveWorkOrder()
    Dim wsActive As Worksheet
    Dim wsLog As Worksheet
    Dim LastRowLog As Long
    Dim LastRowActive As Long
    Dim BayRow As Long
    Dim LogRow As Long

    On Error GoTo ErrorHandler

    ' Define the worksheets
    Set wsActive = ThisWorkbook.Sheets("Active Bays")
    Set wsLog = ThisWorkbook.Sheets("Work Order Log")

    ' Determine the first available row in Work Order Log
    LastRowLog = wsLog.Cells(wsLog.Rows.Count, "A").End(xlUp).Row
    If wsLog.Cells(LastRowLog, "A").Value = "" Then
        LogRow = 1 ' Start at row 1 if the log is empty
    Else
        LogRow = LastRowLog + 1
    End If

    ' Find the last used row in Active Bays
    LastRowActive = wsActive.Cells(wsActive.Rows.Count, "A").End(xlUp).Row

    ' Loop through Active Bays to transfer rows with "Completed" status
    For BayRow = 2 To LastRowActive
        If wsActive.Cells(BayRow, "E").Value = "Completed" Then ' Assuming "Status" is column E
            ' Write the row to the Work Order Log
            wsLog.Rows(LogRow).Value = wsActive.Rows(BayRow).Value
            LogRow = LogRow + 1 ' Move to the next available row

            ' Clear the row in Active Bays
            wsActive.Rows(BayRow).ClearContents
        End If
    Next BayRow

    MsgBox "Work orders successfully saved to log.", vbInformation
    Exit Sub

ErrorHandler:
    MsgBox "An error occurred: " & Err.Description, vbExclamation

End Sub

 

[Micron]

Nothing jumps out at me. Did you step through the code (F8) and watch the flow and check your variable values as you go? Perhaps in one situation or another the starting row value ends up being 5 at the start.

 

[jwevans]

Micron, thank you for your reply. I have stepped through the code and the only thing I see is that it steps over the following: LogRow <span>=</span> <span>1</span> <span>' Start at row 1 if the log is empty</span>

 

[Skyybot]

I always suggest removing On Error statements when debugging.

 

[jwevans]

Thank you, Skyybot. I will give it a try

 

[Micron]

Then this is not true:

If wsLog.Cells(LastRowLog, "A").Value = "" Then

 

[jwevans]

Micron, what would be the fix? I have deleted these rows, added new rows and cleared these rows of all data

 

[Micron]

Off hand I don't know what the fix is. Are you saying it should be "" because you know there is no data in last row of col A?
Any chance you could post a wb copy to a file share and cut to the chase? It would only need to have the involved sheets and code modules.
Or step through until the line after that line that is skipped, then in the immediate window type
?wsLog.Cells(LastRowLog,"A") and hit Enter. If you get a blank line then it should be true. Or try

?wsLog.Cells(LastRowLog,"A") = "" and hit Enter. It should return True or False.

If you don't know of this approach you should read up on debugging in the vba editor. It's basic trouble shooting you should know if you're going to mess with code on a regular basis.