transpose 3d cells to a column in single workbook

J

joeeng

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?
 
B

Bernie Deitrick

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
 
J

joeeng

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?
 
B

Bernie Deitrick

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
 
J

joeeng

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
 
B

Bernie Deitrick

You seem to have answered your own question, but I will still ask: Do you
need further help?

HTH,
Bernie
MS Excel MVP
 
B

Bernie Deitrick

In the Properties window in the VBE (press F4 if it isn't showing) you can
change the module's name property when the module is the selected object in
the object explorer.

HTH,
Bernie
MS Excel MVP
 
B

Bernie Deitrick

Or, if you mean the macro, simply change the string after the keyword Sub:

Sub TryNow()
.....
End Sub

Sub TryNow2()
.....
End Sub

Sub DoColumnA()
.....
End Sub

HTH,
Bernie
MS Excel MVP
 
J

joeeng

That's what I needed.

Thanks for your help.

Bernie Deitrick said:
Or, if you mean the macro, simply change the string after the keyword Sub:

Sub TryNow()
.....
End Sub

Sub TryNow2()
.....
End Sub

Sub DoColumnA()
.....
End Sub

HTH,
Bernie
MS Excel MVP
Click to expand...
 
Top