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?

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?

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?

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?

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?

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

HTH,
Bernie
MS Excel MVP


"joeeng" wrote in message
...
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?

How can I rename the macro module or save it with an alternate name?

"Bernie Deitrick" wrote:

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

HTH,
Bernie
MS Excel MVP


"joeeng" wrote in message
...
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?

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


"joeeng" wrote in message
...
How can I rename the macro module or save it with an alternate name?

"Bernie Deitrick" wrote:

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

HTH,
Bernie
MS Excel MVP


"joeeng" wrote in message
...
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?

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


"joeeng" wrote in message
...
How can I rename the macro module or save it with an alternate name?

"Bernie Deitrick" wrote:

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

HTH,
Bernie
MS Excel MVP


"joeeng" wrote in message
...
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?

That's what I needed.

Thanks for your help.

"Bernie Deitrick" wrote:

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


"joeeng" wrote in message
...
How can I rename the macro module or save it with an alternate name?

"Bernie Deitrick" wrote:

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

HTH,
Bernie
MS Excel MVP


"joeeng" wrote in message
...
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?