Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort and summarize code help request
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort and summarize code help request
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract Date, and summarize data request | Excel Programming | |||
Request for a code | Excel Discussion (Misc queries) | |||
sort doesn't sort numerically 2nd request | Excel Programming | |||
PLEASE HELP ! SORT & SUMMARIZE ? | New Users to Excel | |||
Macro or VB Code to Summarize Info.from 4 workbooks | Excel Programming |