using the consolidation function in VBA

I am hoping someone will be able to help. I want to use a macro that will consolidate a statement. I have clients that make several payments a month, and the funds have to be manually entered into our system. Currently, I change the statement to make it easier for staff to receive, including consolidating the payments. However, this is a manual process, and I am also I am the only one that can complete this task. Using VBA, I will free myself up for other tasks that need to be completed. The code will need to be dynamic as different statements have different amount of payments on it. I have written the code to obtain the dynamic range and have assigned this address to a variable.

Below is a dummy example of they way the information will look.

When I use the Consolidated function in excel the data changes too:

I have ran the Macro recorder, but I was hoping there was a way without have to enter the location of the excel document? i.e. someone may move this document to a different folder and that would break this macro.

Sub Consolidate()
'
' Consolidate Macro
'
    Application.CutCopyMode = False
    Selection.Consolidate Sources:= _
        "'https://Documents/[Statement 8973.xlsm]Receipt'!R2C1:R36C4" _
        , Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False
End Sub

Also, this code uses R1C1 and range of dynamically obtain is using the A1:B200 range (example).

Sorry, I had to create new files, with dummy information. There are three files that I am attaching:

1. VBA Project - Reference File (This file is the file that I pulled the Datasheet from. The original files have two other sheets on it and I don't modify the original workbook.

2. VBA Project - Dummy File (This file is before I have run the VBA Code).

3. VBA Project - Dummy File (VBA Code Processed) This is is what the current results I obtain after running the code.

The coding is probably really bad:( sorry I have just started and have signed up for some courses. There are two modules. The 2nd module mostly codes that I have obtained via Record Macro. I have change it to obtain the ranges dynamically has each statement I process will have a different number of rows.

Thanks for any help:) As getting this done will save me several days a month.