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).