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
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