Sort and summarize code help request

J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top