Make all values of a 3D named range appear on summary sheet

[Joe L]

Hi All, I have a 3D named range (across 145 woksheets). I can use that for
sum, average etc but is it possible to make all the values appear on a
summary sheet?.
I am trying with 3D but also open to other ways. So I have values on H42 on
145 sheets - how to make them link quickly to a summary sheet (so I do not
have to make a formula linking each H42 to the summary sheet
Thanks in advance for any ideas
Joe

 

[Gord Dibben]

Sub List_3D_Values()
Dim Cell As Range
Dim Sh As Worksheet
Dim csh As Worksheet
Set csh = ActiveWorkbook.Worksheets.Add
With csh
.Name = "Summary"
.Range("A1").Value = "Sheet"
.Range("B1").Value = "Value"
End With
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Name <> csh.Name Then
With csh.Range("A65536").End(xlUp).Offset(1, 0)
.Value = Sh.Name
.Offset(0, 1).Value = Sh.Range("H42").Value
End With
End If
Next Sh
End Sub


Gord Dibben MS Excel MVP