Good Morning,
I currently use a workbook that holds our whole works orders database amongst many other things.
However predominantly I use Sheet9 named OrderStatus.
In Column J I have a formula that reads if =IF([@[Revised DueDate]=]=>[@DueDate],TRUE,"").
So column J has around 138 rows but will continue to grow or diminish dependent on parts arriving and the amendment of the revised due date which is changed on an in-house planning tool.
What I want is for if this formula does =TRUE a VBA code to copy the entire Row into sheet 5 named Test, I would like the same lines to be overwritten if there is an amendment and not duplicated.
I have been shopping about trying to complete this myself using variable modified codes similar to the below but just cant get these to work and I cannot see the where my issue lies. Th code runs with no issues however there is nothing copied into Test Sheet. I have also tried using the Sheet5 name instead of Test sheet but no luck.
Any tips or help would be massivley appreciated.
I currently use a workbook that holds our whole works orders database amongst many other things.
However predominantly I use Sheet9 named OrderStatus.
In Column J I have a formula that reads if =IF([@[Revised DueDate]=]=>[@DueDate],TRUE,"").
So column J has around 138 rows but will continue to grow or diminish dependent on parts arriving and the amendment of the revised due date which is changed on an in-house planning tool.
What I want is for if this formula does =TRUE a VBA code to copy the entire Row into sheet 5 named Test, I would like the same lines to be overwritten if there is an amendment and not duplicated.
I have been shopping about trying to complete this myself using variable modified codes similar to the below but just cant get these to work and I cannot see the where my issue lies. Th code runs with no issues however there is nothing copied into Test Sheet. I have also tried using the Sheet5 name instead of Test sheet but no luck.
Code:
Sub MoveRowBasedOnCellValue()
'Updated by Extendoffice 2017/11/10
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("OrderStatus").UsedRange.Rows.Count
J = Worksheets("Test").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Test").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("OrderStatus").Range("J1:J" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = "TRUE" Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Test").Range("A" & J + 1)
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub
Any tips or help would be massivley appreciated.