0
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)
Martin
  • 15,542
  • 1
  • 29
  • 46

0 Answers0