how to consolidate sheets
 

I have for instance 5 sheets (A,B,C,D,E…,many more in reality) which
contain DATA in the same areas.
Sometimes I need to consolidate A+ B
Other times I need to consolidate A +C+D
………….
Now, I am on the active document Z which is with no DATA on it .

I want excel to consolidate on it A +C+D

I would also like to introduce the inputs(name of the sheets to
consolidate) in the following way:
In A1 I type in:A
In A2 I type in:C
In A3 I type in:D

QUESTION:which is the macro which allows me the flexibility to
consolidate the sheets A,D,C, ETC of my choice?

Many thanks

tom


--
tommasopalazzot
------------------------------------------------------------------------
tommasopalazzot's Profile: http://www.excelforum.com/member.php...o&userid=27730
View this thread: http://www.excelforum.com/showthread...hreadid=482527

how to consolidate sheets
 

If you list your sheets in Z, then I assume there is no data in column A of
the other sheets - if there is, do you want to overwrite you list of sheet
names.

Also, do you want to put summation formulas in the consolidated cells, or do
you want to do the addition in code and place just the sum in the cells.

Where is the data area you want to consolidate.

Is it all numbers or are there labels/text. IF so, where is it.

--
Regards,
Tom Ogilvy


"tommasopalazzot"
<tommasopalazzot.1y1l6m_1131225005.5007@excelfor um-nospam.com wrote in
message news:tommasopalazzot.1y1l6m_1131225005.5007@excelf orum-nospam.com...

I have for instance 5 sheets (A,B,C,D,E.,many more in reality) which
contain DATA in the same areas.
Sometimes I need to consolidate A+ B
Other times I need to consolidate A +C+D
.....
Now, I am on the active document Z which is with no DATA on it .

I want excel to consolidate on it A +C+D

I would also like to introduce the inputs(name of the sheets to
consolidate) in the following way:
In A1 I type in:A
In A2 I type in:C
In A3 I type in:D

QUESTION:which is the macro which allows me the flexibility to
consolidate the sheets A,D,C, ETC of my choice?

Many thanks

tom


--
tommasopalazzot
------------------------------------------------------------------------
tommasopalazzot's Profile:
http://www.excelforum.com/member.php...o&userid=27730
View this thread: http://www.excelforum.com/showthread...hreadid=482527

how to consolidate sheets
 

1)yes,there are no data in column A
2)I just want to do the addition in code and place the sum in th
cells
3)data area is in the cells C5:F10,H10:P15
4)only numbers,no tex

--
tommasopalazzo
-----------------------------------------------------------------------
tommasopalazzot's Profile: http://www.excelforum.com/member.php...fo&userid=2773
View this thread: http://www.excelforum.com/showthread.php?threadid=48252

how to consolidate sheets
 

Sub ConsolidateSheets()
Dim sh As Worksheet, rng As Range
Dim rng1 As Range, cell As Range
Dim cell1 As Range
With Worksheets("Z")
Set rng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
Set rng1 = .Range("C5:F10,H10:P15")
rng1.Value = 0
If rng.Count 25 Then
Set rng = .Range("A1")
End If
If IsEmpty(rng) Then Exit Sub
End With
For Each cell In rng
Set sh = Worksheets(cell.Value)
For Each cell1 In rng1
If IsNumeric(sh.Range(cell1.Address).Value) Then
cell1.Value = cell1.Value + sh.Range(cell1.Address)
End If
Next
Next
End Sub

Worked for me.

--
Regards,
Tom Ogilvy




"tommasopalazzot"
<tommasopalazzot.1y1ovy_1131229801.7552@excelfor um-nospam.com wrote in
message news:tommasopalazzot.1y1ovy_1131229801.7552@excelf orum-nospam.com...

1)yes,there are no data in column A
2)I just want to do the addition in code and place the sum in the
cells
3)data area is in the cells C5:F10,H10:P15
4)only numbers,no text


--
tommasopalazzot
------------------------------------------------------------------------
tommasopalazzot's Profile:
http://www.excelforum.com/member.php...o&userid=27730
View this thread: http://www.excelforum.com/showthread...hreadid=482527

how to consolidate sheets
 

Thank you for your solution
before trying it(I am not an expert),could you tell me if it is no
possible to use instead the CONSOLIDATE function?

In the EXCEL FUNCTION REFERENCE booklet of the early '90, I read th
following example:
=consolidate(("c:\excel\sales!r10:c1:r25:c6","c:\e xcel\saleswes!r10:c1:r25:c6"),9,true,true)

How can I tell excel that IN MY CASE, sales! is the content in cell A
,and saleswes! is the content in cell A2?

If your patience is not exhausted,please address this last question

--
tommasopalazzo
-----------------------------------------------------------------------
tommasopalazzot's Profile: http://www.excelforum.com/member.php...fo&userid=2773
View this thread: http://www.excelforum.com/showthread.php?threadid=48252

how to consolidate sheets
 

Possibly you are looking at something to do with xl4macros - there is no
worksheet function Consolidate. You can use the Consolidate... function
under the Data menu, but it doesn't appear to be dynamic.

--
Regards,
Tom Ogilvy


"tommasopalazzot"
<tommasopalazzot.1y2iza_1131268801.6984@excelfor um-nospam.com wrote in
message news:tommasopalazzot.1y2iza_1131268801.6984@excelf orum-nospam.com...

Thank you for your solution
before trying it(I am not an expert),could you tell me if it is not
possible to use instead the CONSOLIDATE function?

In the EXCEL FUNCTION REFERENCE booklet of the early '90, I read the
following example:

=consolidate(("c:\excel\sales!r10:c1:r25:c6","c:\e xcel\saleswes!r10:c1:r25:c
6"),9,true,true)

How can I tell excel that IN MY CASE, sales! is the content in cell A1
,and saleswes! is the content in cell A2?

If your patience is not exhausted,please address this last question!


--
tommasopalazzot
------------------------------------------------------------------------
tommasopalazzot's Profile:
http://www.excelforum.com/member.php...o&userid=27730
View this thread: http://www.excelforum.com/showthread...hreadid=482527