EV

Sub CompareAndAddValues()
    Dim wsWAR As Worksheet
    Dim wsActuals As Worksheet
    Dim lastRowWAR As Long
    Dim lastRowActuals As Long
    Dim i As Long, j As Long
    Dim found As Boolean
    Dim monthName As String
    Dim actualsTable As ListObject
    Dim newRow As ListRow
    
    ' Get the current month name
    monthName = Format(Date, "mmm")
    
    ' Set the worksheets
    Set wsWAR = ThisWorkbook.Sheets("WAR " & monthName)
    Set wsActuals = ThisWorkbook.Sheets("Actuals")
    
    ' Check if "Actuals" sheet has a table
    On Error Resume Next
    Set actualsTable = wsActuals.ListObjects(1) ' Assuming there's only one table
    On Error GoTo 0
    
    ' If no table exists, show an error and exit
    If actualsTable Is Nothing Then
        MsgBox "No table found in 'Actuals' sheet. Please convert the data range into a table.", vbCritical
        Exit Sub
    End If
    
    ' Find the last row in WAR sheet
    lastRowWAR = wsWAR.Cells(wsWAR.Rows.Count, "Q").End(xlUp).Row
    
    ' Loop through each value in column Q of "WAR " & monthName
    For i = 1 To lastRowWAR
        found = False
        
        ' Skip "Type of Work" and "Grand Total"
        If wsWAR.Cells(i, "Q").Value = "Type of Work" Or wsWAR.Cells(i, "Q").Value = "Grand Total" Then
            GoTo NextIteration
        End If
        
        ' Loop through each value in column B of "Actuals" table
        For j = 1 To actualsTable.ListRows.Count
            If wsWAR.Cells(i, "Q").Value = actualsTable.DataBodyRange.Cells(j, 2).Value Then
                found = True
                Exit For
            End If
        Next j
        
        ' If the value is not found, add it as a new row in the table
        If Not found Then
            Set newRow = actualsTable.ListRows.Add
            newRow.Range.Cells(1, 2).Value = wsWAR.Cells(i, "Q").Value
            newRow.Range.Cells(1, 2).Interior.Color = RGB(144, 238, 144) ' Light green color
        End If
        
NextIteration:
    Next i
    
    MsgBox "Comparison and addition complete!"
End Sub

Sub CompareAndAddValues()
    Dim wsWAR As Worksheet
    Dim wsActuals As Worksheet
    Dim lastRowWAR As Long
    Dim i As Long, j As Long
    Dim found As Boolean
    Dim monthName As String
    Dim actualsTable As ListObject
    Dim newRow As ListRow
    Dim lastTableRow As Range
    
    ' Get the current month name
    monthName = Format(Date, "mmm")
    
    ' Set the worksheets
    Set wsWAR = ThisWorkbook.Sheets("WAR " & monthName)
    Set wsActuals = ThisWorkbook.Sheets("Actuals")
    
    ' Check if "Actuals" sheet has a table
    On Error Resume Next
    Set actualsTable = wsActuals.ListObjects(1) ' Assuming there's only one table
    On Error GoTo 0
    
    ' If no table exists, show an error and exit
    If actualsTable Is Nothing Then
        MsgBox "No table found in 'Actuals' sheet. Please convert the data range into a table.", vbCritical
        Exit Sub
    End If
    
    ' Find the last row in WAR sheet
    lastRowWAR = wsWAR.Cells(wsWAR.Rows.Count, "Q").End(xlUp).Row
    
    ' Loop through each value in column Q of "WAR " & monthName
    For i = 1 To lastRowWAR
        found = False
        
        ' Skip "Type of Work" and "Grand Total"
        If wsWAR.Cells(i, "Q").Value = "Type of Work" Or wsWAR.Cells(i, "Q").Value = "Grand Total" Then
            GoTo NextIteration
        End If
        
        ' Loop through each value in column B of "Actuals" table
        For j = 1 To actualsTable.ListRows.Count
            If wsWAR.Cells(i, "Q").Value = actualsTable.DataBodyRange.Cells(j, 2).Value Then
                found = True
                Exit For
            End If
        Next j
        
        ' If the value is not found, add it to the last empty row or insert a new row
        If Not found Then
            ' Get the last row of the table
            Set lastTableRow = actualsTable.DataBodyRange.Rows(actualsTable.ListRows.Count)
            
            ' Check if the last row in column B is empty
            If Trim(lastTableRow.Cells(1, 2).Value) = "" Then
                ' If empty, use the existing last row
                lastTableRow.Cells(1, 2).Value = wsWAR.Cells(i, "Q").Value
                lastTableRow.Cells(1, 2).Interior.Color = RGB(144, 238, 144) ' Light green color
            Else
                ' If not empty, add a new row
                Set newRow = actualsTable.ListRows.Add
                newRow.Range.Cells(1, 2).Value = wsWAR.Cells(i, "Q").Value
                newRow.Range.Cells(1, 2).Interior.Color = RGB(144, 238, 144) ' Light green color
            End If
        End If
        
NextIteration:
    Next i
    
    MsgBox "Comparison and addition complete!"
End Sub