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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!