Hey Guys! I am new to this forum and just started dabbling in VBA/Macros over the last couple weeks. I have a workbook that uses two main tabs. One tab is known as "Tracking" (items in process or being worked on) and the other is known as "Completed" (items that are finished). I have written a macro that finds dates under the "Completion Date" column (column J in my worksheet) in the Tracking tab and if there is a date in any of those cells, it cuts that row out of Tracking and pastes it into the next available row in the "Completed" tab.
With all that said, I am getting a Run-time error '424': object required. Here is the code that I have thus far:
Sub Request_Complete()
Dim rngDest As Range
Set rngDest = ws.Range("rngDest")
' Limit the trap area to range of cells in which completed dates are entered as defined above
If Not Intersect(Target, ws.Range("rngTrigger")) Is Nothing Then
' Only trigger if the value entered is a date or is recognizable as a valid date
If IsDate(Target) Then
'Ensure subsequent deletion of 'moved' row does NOT cause the Change Event to run again and get itself in a loop!
Application.EnableEvents = False
Target.EntireRow.Select
Selection.Cut
rngDest.Insert Shift:=xlDown
Selection.Delete
' Reset EnableEvents
Application.EnableEvents = True
End If
End If
End Sub
When I debug the error it takes me to the Set rngDest = ws.Range ("rngDest") row. I have changed the "ws" to Tracking and Completed so it references the names of my tabs but then I get a 1004 error. Any thoughts? Thank you!
Bookmarks