How to Transfer data from table to another in 2 different worksheets?

I have 2 tables A & B. Table A is a summary and also is the data source for table B which represents a breakdown of table A.

What I want to achieve is that anything entered in columns (Exceptional, Reserve, Percentage) in Table A Reflects in Table B accordingly.

I have created a class module to read the members in Table A and reserve the values during the loop into a key dictionary.

the code works fine but the outputs come as consolidated results in Table B.

It would be ideal to do a search first on the reserved account numbers in Table B and then start pasting the data from the key dictionary. Thank you

'declare variables in the class module
Option Explicit

Public Acc_Num As String
Public Exceptional As String
Public Reserve As String
Public Percentage As Double

Sub test()
    Dim dict As New Dictionary

    Dim rg As Range, rd As Range
    Set rg = Intersect(Sheets("DataSource").UsedRange, Sheets("DataSource").Range("C5:H50"))
    Set rd = Intersect(Sheets("OutPut").UsedRange, Sheets("OutPut").Range("C5:J50"))
    ' Read through the data
    Dim i, z As Long, y As Variant, row As Long, cell As Variant
    Dim Acc_Num As String, Cust_Accounts As ACC
    For i = 1 To rg.Rows.Count
        For z = 1 To rd.Rows.Count
            'row = 1
            Acc_Num = rg.Cells(i, 1).Value

            If dict.Exists(Acc_Num) = False Then
                Set Cust_Accounts = New ACC
                Cust_Accounts.Acc_Num = Acc_Num
                dict.Add Key:=Cust_Accounts.Acc_Num, Item:=Cust_Accounts
            Else

                Set Cust_Accounts = dict(Acc_Num)
            End If
            
            For Each y In dict.Keys
            
                ' Update the values
                With Cust_Accounts
                    .Exceptional = .Exceptional + rg.Cells(i, 4).Value
                    rd.Cells(z, 6).Value = .Exceptional
                    .Reserve = .Reserve + rg.Cells(i, 5).Value
                    rd.Cells(z, 7).Value = .Reserve
                    .Percentage = .Percentage + rg.Cells(i, 6).Value
                    rd.Cells(z, 8).Value = .Percentage
            
                End With
        
            Next y
            row = row + 1
        Next z
    Next i
                    
End Sub

Using CustomerId & AgedDebt as the key for the Dictionary will allow you to create a one to one relationship between the tables.

In my code I used enumerations to name the columns of the tables, arrays instead of ranges to make it more efficient, and simplified the main code by moving some tasks into their own functions.

Option Explicit

Public Enum DataSourceColumn
    dsCustomerNumber = 1
    dsAgedDebt = 3
    dsExceptional
    dsReserve
    dsPercentage
End Enum

Public Enum OutPutColumn
    otCustomerNumber = 1
    otAgedDebt = 5
    otExceptional
    otReserve
    otPercentage
End Enum

Private Enum ConsolidatedDataColumns
    cdCustomerNumber = 1
    cdExceptional = 2
    cdReserve
    cdPercentage
End Enum


Function wsDataSource() As Worksheet
    Set wsDataSource = ThisWorkbook.Worksheets("DataSource")
End Function

Function wsOutPut() As Worksheet
    Set wsOutPut = ThisWorkbook.Worksheets("OutPut")
End Function

Function DataSourceTable() As ListObject
    Set DataSourceTable = wsDataSource.ListObjects(1)
End Function

Function OutPutTable() As ListObject
    Set OutPutTable = wsOutPut.ListObjects(1)
End Function

Function getDataSourceCustomerIdIndexMap() As Scripting.Dictionary
    Dim SourceData As Variant
    SourceData = DataSourceTable.DataBodyRange.Value
    
    Dim DataSourceCustomerIdIndexMap As New Scripting.Dictionary
    Dim CustomerId As String
    Dim AgedDebt As String
    Dim Key As String
    Dim r As Long
    
    For r = 1 To UBound(SourceData)
        CustomerId = SourceData(r, DataSourceColumn.dsCustomerNumber)
        AgedDebt = SourceData(r, DataSourceColumn.dsAgedDebt)
        Key = getCustomerNumberAgedDebtKey(CustomerId, AgedDebt)
        DataSourceCustomerIdIndexMap.Add Key:=Key, Item:=r
    Next
    Set getDataSourceCustomerIdIndexMap = DataSourceCustomerIdIndexMap
End Function

Sub UpdateOutputTable()
    Dim SourceData As Variant
    SourceData = DataSourceTable.DataBodyRange.Value
    Dim DataSourceCustomerIdIndexMap As Scripting.Dictionary
    Set DataSourceCustomerIdIndexMap = getDataSourceCustomerIdIndexMap
    
    Dim OutPutData As Variant
    OutPutData = OutPutTable.DataBodyRange.Value
    Dim CustomerId As String
    Dim AgedDebt As String
    Dim Key As String
    Dim r As Long
    For r = 1 To UBound(OutPutData)
        CustomerId = OutPutData(r, OutPutColumn.otCustomerNumber)
        AgedDebt = OutPutData(r, OutPutColumn.otAgedDebt)
        Key = getCustomerNumberAgedDebtKey(CustomerId, AgedDebt)
        If DataSourceCustomerIdIndexMap.Exists(Key) Then
            Dim SourceRow As Long
            SourceRow = DataSourceCustomerIdIndexMap(Key)
            OutPutData(r, OutPutColumn.otExceptional) = SourceData(SourceRow, DataSourceColumn.dsExceptional)
            OutPutData(r, OutPutColumn.otReserve) = SourceData(SourceRow, DataSourceColumn.dsReserve)
            OutPutData(r, OutPutColumn.otPercentage) = SourceData(SourceRow, DataSourceColumn.dsPercentage)
        End If
    Next
    OutPutTable.DataBodyRange.Value = OutPutData
End Sub

Private Function getCustomerNumberAgedDebtKey(CustomerId As String, AgedDebt As String)
    getCustomerNumberAgedDebtKey = CustomerId & "|" & AgedDebt
End Function