Summarize multiple worksheet detail on summary sheet

[061931]

I have a workbook that has up to 31 worksheets named 1, 2, 3....which
contains db log information for each day of the month.
Each worksheet has 2 columns (ColA=text, ColB=number):
Worksheet1
ColA..............ColB
TxtMsg1..........22
TxtMsg3..........1
TxtMsg4..........10
TxtMsg4..........3
TxtMsg7 .........24
etc.......

Worksheet2
ColA..............ColB
TxtMsg1..........3
TxtMsg2..........8
TxtMsg5..........9
TxtMsg6..........3
TxtMsg7 .........4
TxtMsg7..........2
etc.......

Worksheet3, Worksheet4, .............

How would I create a Month Summary page that shows the sum of each unique
value in ColA from all the sheets combined?
Monthly Summary Sheet
ColA...........ColB
TxtMsg1.......25
TxtMsg2.......8
TxtMsg3.......1
TxtMsg4.......13
TxtMsg5........9
TxtMsg6........3
TxtMsg7.......30
etc....

TIA,
Don

 

[Guest]

Check out Data>Consolidate and get back to us with any questions. I believe
it will do what you want.

 

[061931]

Will I ever learn everything that Excel is capable of?!?! Thanks Ron

Is there a way to automate this with functions or VBA, so a user doesnt have
to setup the Consolidate References each month for the varying 28, 29, 30 or
31 day months and the varying number of rows in the spreadsheets?

TIA,
Don

 

[Guest]

I believe that, through judicious use of range names, you won't need to
re-adjust the ranges to be consolidated. Since you'd be referencing Named
Ranges, and not cell references, the Consolidation would pick up the correct
information.

Example:
The range A1:C50 on Sheet1 of each workbook1 could be named rngMthData.
The range A1:C73 on Sheet1 of each workbook2 could be named rngMthData.

The next month, set each rngMthData range to refer to the appropriate ranges.

Does that give you something to work with?

 

[061931]

Guess I dont quite understand your method. I have 1 workbook with 28, 29,
30, or 31 worksheets depending on the month. Each worksheet has a varying
number of rows, but the data is always in ColA & ColB in the same format.
Using the same Name>Define didnt work on the worksheets in the same
workbook.
Maybe this will help...I recorded a test macro of your original
Data>Consolidation suggestion that works great. Just was looking for a more
automated way of doing it.

Sub Consolidate()
'
Range("A1").Select '<<<<<<<<<<On my Summary sheet.
Selection.Consolidate Sources:=Array( _
"'E:\Directory\My Documents\[TabTest.xls]1'!R1C1:R12C2", _
"'E:\Directory\My Documents\[TabTest.xls]2'!R1C1:R25C2", _
"'E:\Directory\My Documents\[TabTest.xls]3'!R1C1:R20C2", _
"'E:\Directory\My Documents\[TabTest.xls]4'!R1C1:R14C2", _
"'E:\Directory\My Documents\[TabTest.xls]5'!R1C1:R21C2", _
"'E:\Directory\My Documents\[TabTest.xls]6'!R1C1:R12C2", _
"'E:\Directory\My Documents\[TabTest.xls]7'!R1C1:R9C2"), Function _
:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False
End Sub

TIA,
Don

 

[Guest]

Take this one for a test drive and let me know how it works:

'************
'START OF CODE
'************
Option Explicit
Option Base 1

Sub Consolidate()
Dim intDayCount As Integer
Dim arrSrcs() As Variant
Dim intCtr As Integer
Dim strPath As String
Dim strFName As String
Dim strConsRange As String
Dim strSrcs As String
Dim strNewSrc As String

strPath = "E:\Directory\My Documents\"
strFName = "TabTest.xls"
strConsRange = "R1C1:R12C2"

intDayCount = [A1].Value

ReDim arrSrcs(intDayCount)
strSrcs = ""
For intCtr = 1 To intDayCount
arrSrcs(intCtr) = "'" & strPath & "[" & strFName & "]" & CStr(intCtr)
& "'!" & strConsRange

Next intCtr
Range("A2:B2").Select '<<<<<<<<<<On my Summary sheet.

Selection.Consolidate Sources:=Array(arrSrcs()), _
Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

End Sub
'**********
'END OF CODE
'**********

NOTE: I changed the process so you put the number of days in A1 and
consolidate at cells A2:B2.

I hope that works

 

[061931]

Ron,

I had to change TopRow:=True to TopRow:=False, but otherwise it works
perfectly.

Thanks for your help!!

Don