Sub-total using Advanced Filter?

[Ray]

Hi -

I have a workbook that's produced daily, including 3 sheets with
customer-level order information. Each sheet is structured exactly
the same way -- the info I need is in columns G:M. Column G is what
you might call the 'key', as it contains the value (State-County-State-
Zip) to be consolidated on.

I need to create a consolidation sheet which will pull a list of
unique places (column G) from each of the 3 data sheets, and then sub-
total the line-items on those entries. In other words, the macro
needs to pull all of the unique values in Column G (on sheet 1), put
it on my Consolidation sheet (in col A) .... then, in Col B, sub-
total based on the unique entries.

Then, go to sheet 2 and repeat the whole process, appending the unique
entries to the bottom of the Consolidation page... and again with
Sheet 3. In the end, the Consolidation page should consist of sub-
totals of entries for each unique place.

Does that make sense?

Any help is GREATLY appreciated!

Thanks, ray

 

[Joel]

I have a few questions

1) Is there only 3 sheets in each workbook. If more than 3 workbooks I need
to know how to identify the 3 sheets
2) You say you need Columns G:M, but g contains the Key. Which columns
contain the number that need to be total.
3) How do the subtotals get added. if your data is in columns H:M (skip g
which is the key) which is 6 columns, then you want the data place in columns
B which is only 3 columns. How do 6 numbers get added and only get 3
results.

 

[Ray]

Thanks for replying Joel ...

Some good questions ...

1) No, there are more than 3 worksheets ... each one will end in
'data' (eg '159 data', '161 data'). Always 8 digits total, last 4 are
'data'.

2 & 3) basically the same issue ... I can't type. Column G contains
the Key, with Columns H:I containing the data. The Consolidation
sheet would then have same type of data (Column G contains unique
locations, H:I sub-totaled data) in the same columns.

Does that clarify better? Sorry for confusion!

//ray

 

[Joel]

Try this code

Sub consolidate()

'check if consolidation sheet exists
Found = False
For Each sht In Sheets
If sht.Name = "Consolidation" Then
Found = True
Exit For
End If

Next sht

If Found = False Then
Sheets.Add after:=Sheets(Sheets.Count)
Set ConSolSht = ActiveSheet
ConSolSht.Name = "Consolidation"
Else
Set ConSolSht = Sheets("Consolidation")
End If

NewRow = 1
For Each sht In Sheets
With sht
If Right(UCase(sht.Name), 4) = "DATA" Then
'copy header row
If NewRow = 1 Then
.Rows(1).Copy Destination:=ConSolSht.Rows(1)
NewRow = 2
End If

RowCount = 2
Do While .Range("G" & RowCount) <> ""
Location = .Range("G" & RowCount)
H_Data = .Range("H" & RowCount)
I_Data = .Range("I" & RowCount)
With ConSolSht
'search for ID
Set c = .Columns("G").Find(what:=Location, _
LookIn:=xlValues, lookat:=xlWhole)

If c Is Nothing Then
'add new row to consolidation sheet
.sht.Rows(RowCount).Copy _
Destination:=.Rows(NewRow)
NewRow = NewRow + 1

Else
'row exists add totals
.Range("H" & c.Row) = H_Data
.Range("I" & c.Row) = I_Data

End If
End With

RowCount = RowCount + 1
Loop

End If
End With
Next sht

End Sub

 

[Joel]

I forgot to add the numbers . The code is just copying the numbers

Make this change


from
'row exists add totals
.Range("H" & c.Row) = H_Data
.Range("I" & c.Row) = I_Data
to
'row exists add totals
.Range("H" & c.Row) = .Range("H" & c.Row) + H_Data
.Range("I" & c.Row) = .Range("I" & c.Row) + I_Data