Sub HOT()
Dim row As Integer
Dim row2 As Integer
Dim HOT As Worksheet
Dim INTRANSIT As Worksheet
Set INTRANSIT = ThisWorkbook.Sheets("INTRANSIT")
Set HOT = ThisWorkbook.Sheets("HOT")
Dim erow As Long
Dim transitRow As Integer
Dim hotRow As Integer
erow = HOT.Range("C" & Rows.Count).End(xlUp).row + 1
Dim yes As Integer
hotRow = Worksheets("HOT").UsedRange.Rows.Count
transitRow = Worksheets("INTRANSIT").UsedRange.Rows.Count
For row = 3 To transitRow:
yes = 0
If IsEmpty(Worksheets("INTRANSIT").Cells(row, 15).Value) <> True Then
For row2 = 2 To hotRow:
If (Worksheets("INTRANSIT").Cells(row, 3).Value = Worksheets("HOT").Cells(row2, 3).Value And Worksheets("INTRANSIT").Cells(row, 5).Value = Worksheets("HOT").Cells(row2, 5).Value And Worksheets("INTRANSIT").Cells(row, 6).Value = Worksheets("HOT").Cells(row2, 6).Value) Then
yes = 1
End If
Next
If yes = 0 Then
INTRANSIT.Range(Cells(row, 1), Cells(row, 15)).Copy Destination:=HOT.Range("A" & erow)
End If
End If
Next
For row2 = 2 To hotRow:
yes = 0
For row = 3 To transitRow:
If (Worksheets("INTRANSIT").Cells(row, 3).Value = Worksheets("HOT").Cells(row2, 3).Value And Worksheets("INTRANSIT").Cells(row, 5).Value = Worksheets("HOT").Cells(row2, 5).Value And Worksheets("INTRANSIT").Cells(row, 6).Value = Worksheets("HOT").Cells(row2, 6).Value) Then
yes = 1
End If
Next
If yes = 0 Then
Worksheets("HOT").Rows(row2).EntireRow.Delete
End If
Next
Application.OnTime DateAdd("s", 10, Now), "HOT"
MsgBox "Update"
End Sub
In this code whenever I run it at first it is fine. But as soon as I add another entry in my "INTRANSIT" worksheet and switch over my "HOT" worksheet I get the 1004 Error with the problem being highlighted as this line:
INTRANSIT.Range(Cells(row, 1), Cells(row, 15)).Copy Destination:=HOT.Range("A" & erow)