how to consolidate sheets

[tommasopalazzot]

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

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

Many thanks

tom

 

[Tom Ogilvy]

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]

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,H1015
4)only numbers,no tex

 

[Tom Ogilvy]

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,H1015")
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]

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:\excel\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

 

[Tom Ogilvy]

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"