Sort and summarize code help request

[joecrabtree]

All,

I have the code below:

What I want to be able to do is for all the worksheet names in the
workbook that begin with 'DATA' is to summarize the data in column A
and B, and the output it in the output sheet.

For example the kind of data I have is:

Worksheet name = DATA010209

CODE VALUE
XXX 1
XXX 22
XXX 21
XXX 45
BBB 64
NNN 54
AAA 64

Worksheet name = DATA040209

CODE VALUE

NNN 54
AAA 64

Using VBA how can I add up all the codes and produce an output
total in a worksheet labelled 'output'?

For example in this case the worksheet 'output' would display

DATA010209

XXX 89
BBB 64
NNN 54
AAA 64

DATA040209

NNN 54
AAA 64

The code I have is as follows:

Sub SummarizeData()



For Each mySh In Worksheets

If Left(mySh.Name, 5) = "DATA " Then

With Sheets("Data")
LastRow = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
Set CodeRange = .Range("A2:A" & LastRow)
Set SumRange = .Range("B2:B" & LastRow)
End With
Set CriteriaRange = Sheets("Output").Range("A2")

Total = WorksheetFunction.SumIf(CodeRange, CriteriaRange, SumRange)
Sheets("Output").Range("B2") = Total



End Sub


Thanks in advance for your help,

Regards

Joe Crabtree

 

[Bernd P]

Hello Joe,

I suggest to use my UDF Sfreq:
http://www.sulprobil.com/html/sfreq.html

Regards,
Bernd