Generating a Final List From Multiple Sheets in Alphabetical Order Using A Macro

Ratiocination · Mar 10th 2013

Hi, So how do I do it? This is a follow on from the other thread where I asked if it was possible to do this without a macro. It seems it is possible but requires alot of space and a pivottable Etc.

Can anyone help me to write a macro for this? I'm new to this so what is simple to you will be foreign to me!

I need to generate a list on the results page that take account of all materials entered into cells A5:A20 and their Quantity in column C5:C20 on every sheet, total them, remove duplicates but still add quantities of the same material should a duplicate be made.

For example sheet1 cement = 5, sheet2 cement = 3, sheet3 cement = 30, etc. The list should have just one entry for cement, but the result should be - cement = 38

I got some way to doing this with formulas and a pivottable but am sure this can be much simpler with a macro...

Thanks, Richard

(Can you explain a few of the basics of VBA or direct me somewhere that can, I am eager to learn)

FDibbins · Mar 10th 2013

Re: Generating a Final List From Multiple Sheets in Alphabetical Order Using A Macro

Hi

Take a look at the attached. What I did was to add a list of all items to each sheet - I did this by grouping all your sheets, and then pasting the list onto 1 sheet, so that it would "punch" through to all sheets. (you can use this same method if you need to add items.

I then used a simple vlookup() to the list (still in group mode). Finally I used a simple =sum() to add all items across all sheets

dangle · Mar 10th 2013

Re: Generating a Final List From Multiple Sheets in Alphabetical Order Using A Macro

Hello Richard

A possible macro which might help:

Code

Sub Consolidate()
Dim a, i As Long, ws As Worksheet
With CreateObject("Scripting.Dictionary")
    For Each ws In Worksheets
        If ws.Name <> "Result" Then
            With ws
                a = .UsedRange.Value
            End With
            For i = 1 To UBound(a, 1)
                If a(i, 1) <> "" And a(i, 1) <> "Quote Sheet" And a(i, 1) <> "Materials" Then
                    If Not .exists(a(i, 1)) Then
                        .Item(a(i, 1)) = a(i, 3)
                    Else
                        .Item(a(i, 1)) = .Item(a(i, 1)) + a(i, 3)
                    End If
                    
                End If
            Next
        End If
    Next
    Sheets("Result").Cells(1).Resize(.Count, 2).Value = Application.Transpose(Array(.Keys, .Items))
End With
End Sub

Display More

There are plenty of help/training files on OzGrid itself if you want to delve into VBA further.

Generating a Final List From Multiple Sheets in Alphabetical Order Using A Macro

Participate now!

Share