• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to consolidate data

Hello, I have some numeric data contained in two worksheets in a workbook. I would like a macro that consolidates (sums) the data from these two sheets into a summary sheet. I know the very basic elements of VBA but I don't have the skills to create a macro to sum the data. Hopefully the attached spreadsheet example will explain.
List Sheet
Will contain the Command button to consolidate the information from Data 1 and Data 2 sheet
Data 1 Sheet
Contains up to 13 sets of data (Location 1 to Location 13) with 6 sets of information per location (maybe more) but the data will be in the sheet
4 sets of data shown in the example
Data 2 Sheet
Contains up to 13 sets of data (Location 1 to Location 13) with 3 sets of information per location (maybe more) but the data will be in the sheet
4 sets of data shown in the example
Summary Sheet
This contains the consolidated (summed) data.
For example, Cell C3 in the summary sheet contains the summed data from (Stage 1, Type A from Location 1 + Stage 1, Type A from Location 2 etc).
I would like to set up a macro command that sums the "Type X" data from each Stage column for each Location and places the result in the Summary sheet.
 

Attachments

  • Chandoo Consolidate.xlsx
    18.3 KB · Views: 8
Hi, according to your attachment a good readers only VBA demonstration as a beginner starter to paste to the Summary worksheet module :​
Code:
Sub Demo1()
  Const D = "Data "
    Dim R&, S%, V, L&
        R = 1
        UsedRange.Clear
        Application.ScreenUpdating = False
    For S = 1 To 2
        With Sheets(D & S).UsedRange.Columns
             V = Application.Match("Location *", .Item(1), 0)
             If IsError(V) Then Beep: Application.ScreenUpdating = True: Exit Sub
            .Cells(V, 2).Resize(, .Count - 1).Copy Cells(R, 1)
            .Item(2).Copy Cells(R, 1)
        With Range(Cells(R, 1), Cells(Rows.Count, 1).End(xlUp))
            .RemoveDuplicates 1, xlNo
            .Cells(1).Value2 = D & S
             L = .Cells(1).End(xlDown).Row
        End With
            Cells(R + 1, 2).Resize(L - R, .Count - 2).Formula = _
                "=SUMIF(" & .Item(2).Address(, , , 1) & ",$A" & R + 1 & "," & .Item(3).Address(, 0, , 1) & ")"
        End With
            R = L + 2
    Next
        Application.ScreenUpdating = True
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Yes, I think I understand the concept. I'm trying to create a spreadsheet for a user who is not very familiar with using Excel. Thanks.
 
...and I love your quote "Never argue with an idiot, he'll bring you down to his level - then beat you with experience … " Been there, done that !!
 
Back
Top