Thanks, that worked. Now can I rename this module so that i can set up
several versions which load different 3d ranges into different columns of the
summary worksheet. I have figured out which parameter to change to change
the column -- change A65536 to B65536.

Thanks

"Bernie Deitrick" wrote:

Of course. You can do anything! (almost ;-))

Change

dataSht.Range("A65536").End(xlUp)(2).Value = _
myCell.Value

to

dataSht.Range("A65536").End(xlUp)(2).Formula = _
"=" & myCell.Address(False, False, xlA1, True)

HTH,
Bernie
MS Excel MVP


"joeeng" wrote in message
...
Thanks, but this will not update the values if the referenced 3d cells
change. I guess that I really want to convert cell references to a column
of
links to the 3d cells. Can this be done?

"Bernie Deitrick" wrote:

Insert a worksheet named Summary, then select the 3D range, and run the
macro below.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim mySht As Worksheet
Dim dataSht As Worksheet
Dim myRange As Range
Dim myCell As Range

Set dataSht = Worksheets("Summary")
Set myRange = Selection

For Each mySht In ActiveWindow.SelectedSheets
For Each myCell In mySht.Range(myRange.Address)
dataSht.Range("A65536").End(xlUp)(2).Value = _
myCell.Value
Next myCell
Next mySht

End Sub



"joeeng" wrote in message
...
I would like to extract 3d cells from a set of worksheets into a single
column on another worksheet within the same workbook. How can I do
this?