Excel VB: How to convert multiple 'for' + 'if' loops to 'for each' cell + 'for' loop

Im getting to grasp the basics of VBA and have automated a few things, however, I think I can step up my game, hence I have a question.

I have a Excel workbook where on one sheet ('Source') I update a number of bank balances on a daily basis using a power query. On a second sheet ('ThisWeek') I combine all the bank balances for the entire week.

Data looks as follows: Source:

|---------------------|------------------|--------------------|------------------|
|      Account Code   | Entity Code      | Balance Yesterday  | Balance Today    |
|---------------------|------------------|--------------------|------------------|
|       Account 1     |  Account Holder  |    Value 1         |    Value 2       |
|---------------------|------------------|--------------------|------------------|

ThisWeek

|  Monday             | 
|---------------------|------------------|--------------------|------------------|
|      Account Code   | Entity Code      | Date    Yesterday  | Date Today       |
|---------------------|------------------|--------------------|------------------|
|       Account 1     |    Account name  |*Insert value 1 here| and Value 2 here |
|---------------------|------------------|--------------------|------------------|
|Tuesday              |
|---------------------|------------------|--------------------|------------------|
|      Account Code   | Entity Code      | Date    Yesterday  | Date Today       |
|---------------------|------------------|--------------------|------------------|
|       Account 1     |    Account name  |*Insert value 1 here| and Value 2 here |
|---------------------|------------------|--------------------|------------------|

In the end I want the macro to copy the daily balances from the source sheet and paste them on the correct day in my weekly overview. i'm not yet looking at pasting at the right date (i.e. daily balances from Wednesday to the Wednesday part in 'ThisWeek', that will come into play later

The common denominator on both sheets is "Account 1", "Account 2" etc. etc.

I now have the below lines of code for each account, which works fine, but I have as much blocks of codes as I have bank accounts. Also not sure if I have to activate the Sheets every time or if this can be simplified as well. A few notes: LastRow = the last row in the 'Source' sheet with a bank balance. Furthermore I now created multiple variables for each bank account (i.e. variable = Account 1). The 'Balance' variable captures the 'Value 1' and 'Value 2' (bank balances of specific account in each loop).

the 'j' at the moment just looks at the first day of my weekly table as I haven't figured out how to make it refer to today's date and compare that to related part in 'ThisWeek'

 Source.Activate

    For i = 2 To LastRow
        
        If Cells(i, 1).Value = Account 1 Then
            Set Balance = Range("C" & i, "D" & i)
            ThisWeek.Activate
                For j = 4 To 26
                
                    If Cells(j, 1).Value = Account 1 Then
                        Range("C" & j, "D" & j) = Balance.Value
                    End If

                Next j
                    
        End If
    
    Next i

I was thinking I could set a range in ThisWeek which would capture all the relevant accounts (Source contains more balances than I'm interested in) and do something like the below. The advantage would be I don't have to declare all the individual accounts anymore and I would reduce the blocks of code.

Set Accounts = ThisWeek.Range("A2:A26")

For Each Cell In Accounts
    For i = 2 To LastRow

        If Cell.Value = Source.Cells(i, 1) Then
            Set Balance = Source.Range("C" & i, "D" & i)
            ThisWeek.Activate
                If Cell.Value = Source.Cells(i, 1) Then
                    ThisWeek.Range("C" & i, "D" & i) = Balance.Value
            End If
        End If
    Next i
Next

This does copy some balances to my 'ThisWeek' sheet, however, it does not paste them after the right account. (i.e. I get Account 1 with the Values of Account 7 of the 'Source' sheet).

Any help on how to align the rights balances with the right accounts?

Sorry for the long post.

Thanks in advance, Thomas