Data Consolidate Limitation

[Len]

Hi,

Consolidate function is useful for budgetary consolidation purpose
However, if there are > 12 worksheets to be consolidated, then
consolidate function is unable to call back and consolidate say 45
worksheets as it has reached the max limit of consolidation ( ie 12
sheets )
In this case, is there any options/alternatives available either on
excel function or excel vba to solve this problem ?


Thanks & Regards
Len

 

[joel]

Can you just run cosolidate on 12 sheets at a time. I have written ver
simple macros (about 30 lines) that can consolidate unlimited number o
sheets. using the column headers and row headers as keys

 

[Len]

Can you just run cosolidate on 12 sheets at a time.  I have written very
simple macros (about 30 lines) that can consolidate unlimited number of
sheets. using the column headers and row headers as keys.

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:http://www.thecodecage.com/forumz/showthread.php?t=174159

Microsoft Office Help

Hi Joel,

Using column & row headers and create link to source data as keys, if
I were to run consolidate for 12 months budget on several times ( ie 7
times for different groups of worksheets ) and then consolidate again
on overall, the results show 25 months including column totals instead
of 12 months result,


Thanks for your help

Regards
Len

 

[joel]

I assume you are running 2007 (I only have 2003). I suspect you ar
using add instead of a merge. Not sure because I haven't ru
consolidate on 2007

 

[Len]

I assume you are running 2007 (I only have 2003).  I suspect you are
using add instead of a merge.  Not sure because I haven't run
consolidate on 2007.

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:http://www.thecodecage.com/forumz/showthread.php?t=174159

Microsoft Office Help

Both excel version 2003 & 2007 post the same result ( ie In office I
run data consolidate using excel 2007 and now at home using excel
2003 )
Further, both version do not have merge worksheet option ( ie only add
or delete option )

Regards
Len

 

[Len]

I assume you are running 2007 (I only have 2003).  I suspect you are
using add instead of a merge.  Not sure because I haven't run
consolidate on 2007.

Microsoft Office Help

Both excel version 2003 & 2007 post the same result ( ie In office I
run data consolidate using excel 2007 and now at home using excel
2003 )
Further, both version do not have merge worksheet option ( ie only add
or delete option )

Regards
Len

Joel, it will great if you could share your excel vba codes to run
data consolidate on unlimited number of worksheets with selection keys
of row & column header, create link to source data

Thanks & Regards
Len

 

[joel]

when you consolidate are you checking both use labels on Row and Column
I usally write the code to consolidate when needed. I try to post a
example tonight

 

[Len]

when you consolidate are you checking both use labels on Row and Column.
I usally write the code to consolidate when needed.  I try to post an
example tonight.

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:http://www.thecodecage.com/forumz/showthread.php?t=174159

Microsoft Office Help

Joel,

Using excel 2003, just testing data consolidate for 6 worksheets of 12
months budget with all selection keys (ie row & column headers and
create link to source data) and post the result on new workbook shows
24 months data consolidation but it works fine for data consolidate on
2 or 3 worksheets of 12 months budget with the same selection keys,
now getting no confidence on using excel data consolidate function

Regards
Len

 

[Len]

when you consolidate are you checking both use labels on Row and Column..
I usally write the code to consolidate when needed.  I try to post an
example tonight.

Microsoft Office Help

Joel,

Using excel 2003, just testing data consolidate for 6 worksheets of 12
months budget with all selection keys (ie row & column headers and
create link to source data) and post the result on new workbook shows
24 months data consolidation but it works fine for data consolidate on
2 or 3 worksheets of 12 months budget with the same selection keys,
now getting no confidence on using excel data consolidate function

Regards
Len

Hi,

I managed to find the same nature of this thread from other forum for
data consolidation by using excel vba and modified the codes to reset
the name range in the worksheet(P+L) e.g. excel file name " ADP.xls"
and name range will be set as "ADP.PL" and this will set the name
range in the worksheet(P+L) for the rest of excel files name with
".PL" , it seems that the codes do not work specially with the
variable "Namerng" & "NameList that do not change when the next file
name is called

At the end, it promts run time error " consolidation reference not
valid "
Can someone help to identify the error of the codes below and rectify
them : -

Const MAXBOOK As Long = 50
Dim i%, SheetArg$()
Dim sPath1 As String
ReDim SheetArg(1 To MAXBOOK)
Dim x As String
Dim Namerng As Variant, NameList As Variant

Dim sPath As String, sFile As String
ThisWorkbook.Worksheets("SumTotal") _
.Cells.ClearContents
sPath = "J:\BBT\LO\Budget\Budget Actual\Acad\"
i = 0
sPath1 = "J:\BBT\LO\Budget\Budget Actual\Acad\*.xls"
sFile = Dir(sPath1)
NameList = Left(sFile, InStrRev(sFile, ".") - 1)
x = ".PL"
Namerng = NameList & x
Do While sFile <> ""
i = i + 1
SheetArg(i) = "'" & sPath & _
"[" & sFile & "]P+L'! Namerng "
sFile = Dir()
Loop
' For i = 1 To MAXBOOK
' Debug.Print i, SheetArg(i)
' Next

ThisWorkbook.Sheets("SumTotal"). _
Range("A1").Consolidate _
Sources:=Array(SheetArg), _
Function:=xlSum, _
TopRow:=True, _
LeftColumn:=True, _
CreateLinks:=True

Thanks in advance

Regards
Len

 

[joel]

this is the code I use. It will only work if the row and column header
exactly match. the code will overwrite data with the exact same heade
and column headers. You may want to tweek the code a little bit fo
your exact requirements.



Sub consolidate()

Set SumSht = Sheets.Add(after:=Sheets(Sheets.Count))
SumSht.Name = "Summary"

NewRow = 2
NewCol = 2
For Each sht In Sheets
If sht.Name <> "Summary" Then

With sht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column

For RowCount = 2 To LastRow
HeaderRow = .Range("A" & RowCount).Value
Set c = SumSht.Columns("A").Find(what:=HeaderRow, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
AddRow = NewRow
SumSht.Range("A" & AddRow).Value = HeaderRow
NewRow = NewRow + 1
Else
AddRow = c.Row
End If

For ColCount = 2 To LastCol
HeaderCol = .Cells(1, ColCount).Value
Data = .Cells(RowCount, ColCount).Value

Set c = SumSht.Rows(1).Find(what:=HeaderCol, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
AddCol = NewCol
SumSht.Cells(1, AddCol).Value = HeaderCol
NewCol = NewCol + 1
Else
AddCol = c.Column
End If

SumSht.Cells(AddRow, AddCol).Value = Data
Next ColCount
Next RowCount
End With
End If
Next sht
End Su

 

[Len]

Joel,

Thanks for your codes and try it out now

At the meantime, just look into my earlier post with sample of vba
codes using data consolidate that try to set name range in "P+L"
worksheet for each excel file and it fails to run, prompts run time
error



Regards
Len