Application.EnableCancelKey = xlInterrupt
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tblManual As ListObject
    On Error Resume Next
    Set tblManual = Me.ListObjects("tbl_manualBought")
    On Error GoTo 0
    If tblManual Is Nothing Then Exit Sub

    Dim refreshNeeded As Boolean
    refreshNeeded = False

    ' Check if rows have been added or deleted:
    Static lastRowCount As Long
    Dim newRowCount As Long
    If Not tblManual.DataBodyRange Is Nothing Then
        newRowCount = tblManual.DataBodyRange.Rows.Count
    Else
        newRowCount = 0
    End If

    Dim previousRowCount As Long
    previousRowCount = lastRowCount
    If lastRowCount = 0 Then
        previousRowCount = newRowCount
    End If

    Dim rngIntersect As Range

    ' Distinguish between row deletion and row addition:
    If newRowCount < previousRowCount Then
        ' Row(s) deleted – Refresh should occur:
        refreshNeeded = True
        Set rngIntersect = tblManual.DataBodyRange
    ElseIf newRowCount > previousRowCount Then
        ' Row added – Do not refresh.
        ' Limit the check to the already existing rows:
        If Not tblManual.DataBodyRange Is Nothing Then
            Set rngIntersect = Application.Intersect(Target, tblManual.DataBodyRange.Resize(previousRowCount))
        End If
        ' No automatic refresh!
    Else
        ' Row count unchanged – perform the normal change check:
        Set rngIntersect = Application.Intersect(Target, tblManual.DataBodyRange)
    End If

    ' Define the columns that should be checked:
    Dim keyCols As Variant
    keyCols = Array("Item Name", "Game", "Amount", "Price", "Currency", "RLM / SCM?", "Date")

    ' Check if the change occurred in a range of the table:
    If Not rngIntersect Is Nothing Then
        Dim cell As Range, headerCell As Range
        Dim tblRowIndex As Long, colIdx As Long, headerName As String

        ' Loop through all changed cells in tbl_manualBought:
        For Each cell In rngIntersect.Cells
            tblRowIndex = cell.Row - tblManual.DataBodyRange.Row + 1
            colIdx = cell.Column - tblManual.Range.Columns(1).Column + 1
            Set headerCell = tblManual.HeaderRowRange.Cells(1, colIdx)
            headerName = CStr(headerCell.Value)

            Dim j As Long, rowComplete As Boolean
            rowComplete = False
            For j = LBound(keyCols) To UBound(keyCols)
                If headerName = keyCols(j) Then
                    rowComplete = True
                    Dim colName As Variant, findHeader As Range, checkCell As Range
                    ' Check all key columns in this row:
                    For Each colName In keyCols
                        Set findHeader = tblManual.HeaderRowRange.Find(What:=colName, LookIn:=xlValues, LookAt:=xlWhole)
                        If findHeader Is Nothing Then
                            rowComplete = False
                            Exit For
                        Else
                            colIdx = findHeader.Column - tblManual.Range.Columns(1).Column + 1
                            Set checkCell = tblManual.DataBodyRange.Cells(tblRowIndex, colIdx)
                            If Len(Trim(CStr(checkCell.Value))) = 0 Then
                                rowComplete = False
                                Exit For
                            End If
                        End If
                    Next colName

                    ' If the entire row (in the relevant columns) is filled, then refresh should occur:
                    If rowComplete Then
                        refreshNeeded = True
                        Exit For
                    End If
                End If
            Next j
            If refreshNeeded Then Exit For
        Next cell
    End If

    ' If a refresh is needed, update tbl_CombinedBought:
    If refreshNeeded Then
        Dim wsCombined As Worksheet
        Dim tblCombined As ListObject
        Set wsCombined = ThisWorkbook.Worksheets("Bought")
        Set tblCombined = wsCombined.ListObjects("tbl_CombinedBought")

        If Not tblCombined.QueryTable Is Nothing Then
            tblCombined.QueryTable.Refresh BackgroundQuery:=False
        Else
            tblCombined.Refresh
        End If
    End If

    ' Update the stored row count for the next run:
    lastRowCount = newRowCount
    Application.EnableCancelKey = xlDisabled
End Sub



---

Application.EnableCancelKey = xlInterrupt
Option Explicit

' Helper function for refreshing the QueryTable of a table on a specific worksheet.
Private Function RefreshQueryTableInSheet(ws As Worksheet, tblName As String) As Boolean
    Dim lo As ListObject
    On Error Resume Next
    Set lo = ws.ListObjects(tblName)
    On Error GoTo 0

    If lo Is Nothing Then
        MsgBox "The table '" & tblName & "' wasn't found in the sheet '" & ws.Name & "'", vbExclamation
        RefreshQueryTableInSheet = False
    Else
        lo.QueryTable.BackgroundQuery = False
        lo.QueryTable.Refresh
        RefreshQueryTableInSheet = True
    End If
End Function

' Helper subroutine for the button macros:
' Refreshes the table and checks the auto value to optionally call another macro.
Private Sub RefreshButtonTable(ws As Worksheet, tblName As String, autoVarName As String, macroToCall As String)
    Dim autoVal As Variant
    If RefreshQueryTableInSheet(ws, tblName) Then
        autoVal = Evaluate(autoVarName)
        If Not IsError(autoVal) Then
            If IsNumeric(autoVal) And autoVal = 1 Then
                Application.Run macroToCall
            End If
        End If
    End If
End Sub

' -------------------------------
' Public macros – still callable separately
' -------------------------------

Public Sub RefreshCurrencyConversions()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Prebackend")
    RefreshQueryTableInSheet ws, "tbl_CurrencyConversion"
End Sub

Public Sub RefreshCompletePricing()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Prebackend")
    RefreshQueryTableInSheet ws, "tbl_CompletePricing"
End Sub

Public Sub RefreshCombinedBought()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Bought")
    RefreshQueryTableInSheet ws, "tbl_CombinedBought"
End Sub

Public Sub RefreshCombinedSold()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sold")
    RefreshQueryTableInSheet ws, "tbl_CombinedSold"
End Sub

Public Sub Refreshbutton_tbl_Buff163SaleImport()
    If RefreshQueryTableInSheet(ThisWorkbook.Worksheets("Prebackend"), "tbl_Buff163SaleHistory") Then
         RefreshButtonTable ActiveSheet, "tbl_Buff163SaleImport", "var_Buff163SaleAutoImport_numberized", "RefreshCombinedSold"
    End If
End Sub

Public Sub Refreshbutton_tbl_Buff163PurchasesImport()
    If RefreshQueryTableInSheet(ThisWorkbook.Worksheets("Prebackend"), "tbl_Buff163PurchasesHistory") Then
         RefreshButtonTable ActiveSheet, "tbl_Buff163PurchasesImport", "var_Buff163PurchasesAutoImport_numberized", "RefreshCombinedBought"
    End If
End Sub

Public Sub Refreshbutton_tbl_SCMPurchasesImport()
    If RefreshQueryTableInSheet(ThisWorkbook.Worksheets("Prebackend"), "tbl_SCMallHistory") Then
         RefreshButtonTable ActiveSheet, "tbl_SCMPurchasesImport", "var_SCMPurchasesAutoImport_numberized", "RefreshCombinedBought"
    End If
End Sub

Public Sub Refreshbutton_tbl_SCMSaleImport()
    If RefreshQueryTableInSheet(ThisWorkbook.Worksheets("Prebackend"), "tbl_SCMallHistory") Then
         RefreshButtonTable ActiveSheet, "tbl_SCMSaleImport", "var_SCMSaleAutoImport_numberized", "RefreshCombinedSold"
    End If
End Sub

Public Sub Refreshbutton_tbl_CSFloatPurchasesImport()
    RefreshButtonTable ActiveSheet, "tbl_CSFloatPurchasesImport", "var_CSFloatPurchasesAutoImport_numberized", "RefreshCombinedBought"
End Sub

Public Sub Refreshbutton_tbl_CSFloatSaleImport()
    RefreshButtonTable ActiveSheet, "tbl_CSFloatSaleImport", "var_CSFloatSaleAutoImport_numberized", "RefreshCombinedSold"
End Sub

Public Sub Refreshbutton_tbl_CSDealsPurchasesImport()
    RefreshButtonTable ActiveSheet, "tbl_CSDealsPurchasesImport", "var_CSDealsPurchasesAutoImport_numberized", "RefreshCombinedBought"
End Sub

Public Sub Refreshbutton_tbl_CSDealsSaleImport()
    RefreshButtonTable ActiveSheet, "tbl_CSDealsSaleImport", "var_CSDealsSaleAutoImport_numberized", "RefreshCombinedSold"
End Sub

Public Sub RefreshCompletePricingAndAgeAndCCYConversions()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Prebackend")

    ' First, refresh the table "tbl_CompletePricing"
    If RefreshQueryTableInSheet(ws, "tbl_CompletePricing") Then
        ' If the refresh was successful, refresh the QueryTables "pCSROIPricingage", "pGeneralPricingAge", and "tbl_CurrencyConversion"
        Call RefreshQueryTableInSheet(ws, "pCSROIPricingage")
        Call RefreshQueryTableInSheet(ws, "pGeneralPricingAge")
        Call RefreshQueryTableInSheet(ws, "tbl_CurrencyConversion")
    End If
    Application.EnableCancelKey = xlDisabled
End Sub