Summarizing Excel Sheets 2

[TaylorTot]

I am working in Excel 2000 and have multiple worksheets I need to summarize onto one page. The worksheets look like:

Date Trade Amount Used Usage code Balance
1/1/04 $200 $200
1/31/04 $50 EU 150
2/4/04 $25 SE 125
3/3/04 $20 BD 105
3/20/04 $10 EU 95

The column Usage code is a drop down box. This is like a form that my end users will be updating each month.

I would like to summarize the Amount Used and Usage Code columns like the following:

Categories Amount Used
EU $60
SE $25
BD $20
Subtotal $105
Total $95

I have tried the following three methods:

1) Pivot Table - This failed because it wouldn't recognize the drop down values
2) VLOOKUP - This failed because it woulded allow a 3d reference
3) If, Then, Else - This also didn't work.

I think I need to use VB but I really am not skilled enough to know where to start. I really would appreciate any help!

Thank you!

 

[SkipVought]

Hi,

Why multiple sheets? One sheet for each user?

Please explain the process up to the summary.

Please explain the objective of the summary.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[TaylorTot]

Hi Skip,

Thanks for the reply.

I have a separate sheet for each customer in a region. One workbook for each market with multiple sheets for each customer.

We would like to be able to summarize the amount of money spent in trade and more specifically in which areas, i.e. usage code.

On each sheet the user will go in after they have used or obtained trade and give the description, date used, amount used, and code (i.e. employee usage (eu), personal (p) etc.)

At the end of each month my accounting director will go in and reconcile this with her books, therefore I need to build a summary page which gathers all of the information onto one page.

 

[SkipVought]

How 'bout this...

Sub Summarize()
'add summary sheet
    Set wsSummary = Worksheets.Add
    i = 0
    For Each ws In Worksheets
        If ws.Name <> wsSummary.Name Then
            If i = 0 Then
                ws.UsedRange.SpecialCells(xlCellTypeVisible).Copy
                wsSummary.Cells(1, 1).PasteSpecial
            Else
                With ws.UsedRange
                    .Range(.Cells(.Row + 1, .Column), .Cells(.Row + .Rows.Count - 1, .Column + .Columns.Count)).SpecialCells(xlCellTypeVisible).Copy
                End With
                With wsSummary.UsedRange
                    .Cells(.Rows.Count + 1, 1).PasteSpecial
                End With
            End If
            i = i + 1
        End If
    Next
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[TaylorTot]

WOW!!! That was really cool!!!

Thank you soooo much!!! I have one other small question. Now that it's all in one spreadsheet can I build a pivot table off of that? I want to have the summary page look like the following:

2003 Trade 1000
2004 Trade 1500
Trade Used:
EU 500
SE 200
BD 150
Subtotal 850
Total: 1650

I'm not sure but I'm guessing I need to build a pivot table for the Trade Used section. . .

Again thank you for your help.

 

[SkipVought]

Yes, a PivotTable could do a great job summarizing your data. I am a big advocate!



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[TaylorTot]

Hi Skip,

I tried to create a pivot table (I only want columns F and G) and it isn't working. I don't know the best way to do this. The put each sheet onto one page, which is great but I have the customer name and address at the beginning of each table therefore I am having a hard time summarizing. Any ideas?

Thank you for your help!

 

[SkipVought]

Please post an example of your source data for the PivotTable.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[TaylorTot]

Do you have an e-mail address I can send you an example to?

 

[SkipVought]

skip.metzger at lmco.com

substitute and delete accordingly.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[nja5150]

How do I modify this code to take the first 5 sheets in the workbook, or alternatively only use the worksheet names I specify.

Thanks for any help!

 

[PHV]

Replace this:
For Each ws In Worksheets
By this:
For j = 1 To 5
Set ws = Worksheets(j)
Or alternatively by this:
For Each s In Array("sheet1", "sheet2", ...)
Set ws = Worksheets(s)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886