I am trying to do a copy "values only" of a complete worksheet into a new
workbook. However, the new copy has #REF in each cell where the original
workbook referenced the INDIRECT function. Is there any way around this?

Also, I get a #NAME in the new copy for each cell that had a used defined
function in the original worksheet.

Thanks, BG

... a copy "values only" of a complete worksheet

If you do an entire source sheet copy paste special values ok over to
a new sheet in a new book, think you shouldn't be getting any error values
...
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"BG" wrote in message
...
I am trying to do into a new
workbook. However, the new copy has #REF in each cell where the original
workbook referenced the INDIRECT function. Is there any way around this?

Also, I get a #NAME in the new copy for each cell that had a used defined
function in the original worksheet.

Thanks, BG

Hi Max,

Thank you for your reply. Please try the following.

Open a new workbook and enter ="This is a "&INDIRECT("Sheet2!A1") into cell
A1 of Sheet1. Enter ="TEST!" into cell A1 of Sheet2. Then go back to Sheet1
and note the words 'This is a TEST!' in cell A1. Then right-click on Sheet1
tab and click on the "Move of Copy..." option. Set "To book:" to the '(new
book)' selection, check the "Create a copy" box and click OK.

In the new workbook created after the OK, you should see a #REF in cell A1
of Sheet1. This is my problem.

Also, if you replace the INDIRECT with the name of a USER defined VBA
function, you will see #NAME rather than #REF. This is my other problem.

In advance, thank you for your help!

BG


"Max" wrote:

... a copy "values only" of a complete worksheet

If you do an entire source sheet copy paste special values ok over to
a new sheet in a new book, think you shouldn't be getting any error values
...
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"BG" wrote in message
...
I am trying to do into a new
workbook. However, the new copy has #REF in each cell where the original
workbook referenced the INDIRECT function. Is there any way around this?

Also, I get a #NAME in the new copy for each cell that had a used defined
function in the original worksheet.

Thanks, BG

The thoughts given earlier were specific to your orig. post's line:
... a copy "values only" of a complete worksheet

Are you still wanting to copy "values only" ?
The copy method which you describe below copies everything, including
formulas which is why you're facing all those errors

To copy "values only" of a complete worksheet,

Select the entire sheet (press CTRL +A)
Right-click Copy

In a new Sheet1 in a new book,
Right-click on A1 Paste special Check "Values" OK

If you need to copy over the formats as well, then just do one mo
Right-click on A1 Paste special Check "Formats" OK
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"BG" wrote in message
...
Hi Max,

Thank you for your reply. Please try the following.

Open a new workbook and enter ="This is a "&INDIRECT("Sheet2!A1") into
cell
A1 of Sheet1. Enter ="TEST!" into cell A1 of Sheet2. Then go back to
Sheet1
and note the words 'This is a TEST!' in cell A1. Then right-click on
Sheet1
tab and click on the "Move of Copy..." option. Set "To book:" to the
'(new
book)' selection, check the "Create a copy" box and click OK.

In the new workbook created after the OK, you should see a #REF in cell A1
of Sheet1. This is my problem.

Also, if you replace the INDIRECT with the name of a USER defined VBA
function, you will see #NAME rather than #REF. This is my other problem.

In advance, thank you for your help!

BG

Hi Max,

Again, thank you for your help and I understand your comments. However,
when I

"Right-click on A1 Paste special Check "Values" OK"

I do not see the "normal" Paste Special options of All, Formulas, Values, etc.

I see a Paste Special window with:

Source: C:\Documents and Settin...\Test.xls Sheet1:R1:R65536 with "Paste"
and "Paste link:" radio buttons and a "As:" window with

Microsoft Excel Worksheet object
Picture
Bitmap
etc.

I am running Excel 2002 (10.6501.6735) SP3

Again, thank you.

BG

"Max" wrote:

The thoughts given earlier were specific to your orig. post's line:
... a copy "values only" of a complete worksheet

Are you still wanting to copy "values only" ?
The copy method which you describe below copies everything, including
formulas which is why you're facing all those errors

To copy "values only" of a complete worksheet,

Select the entire sheet (press CTRL +A)
Right-click Copy

In a new Sheet1 in a new book,
Right-click on A1 Paste special Check "Values" OK

If you need to copy over the formats as well, then just do one mo
Right-click on A1 Paste special Check "Formats" OK
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"BG" wrote in message
...
Hi Max,

Thank you for your reply. Please try the following.

Open a new workbook and enter ="This is a "&INDIRECT("Sheet2!A1") into
cell
A1 of Sheet1. Enter ="TEST!" into cell A1 of Sheet2. Then go back to
Sheet1
and note the words 'This is a TEST!' in cell A1. Then right-click on
Sheet1
tab and click on the "Move of Copy..." option. Set "To book:" to the
'(new
book)' selection, check the "Create a copy" box and click OK.

In the new workbook created after the OK, you should see a #REF in cell A1
of Sheet1. This is my problem.

Also, if you replace the INDIRECT with the name of a USER defined VBA
function, you will see #NAME rather than #REF. This is my other problem.

In advance, thank you for your help!

BG

Really don't know what's happening over there <g, but as a last attempt,
try the alternative to right-clicking on A1 in the new sheet as per below
(my ver is Excel 97)

(Do hang around awhile here for better insights from others ..)

In a new Sheet1 in a new book,

Instead of
Right-click on A1 Paste special Check "Values" OK

With A1 selected,
click Edit Paste special check "Values" OK

If you need to copy over the formats as well, then just do one mo

Instead of
Right-click on A1 Paste special Check "Formats" OK

With A1 selected,
click Edit Paste special check "Formats" OK

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"BG" wrote in message
...
Hi Max,

Again, thank you for your help and I understand your comments. However,
when I

"Right-click on A1 Paste special Check "Values" OK"

I do not see the "normal" Paste Special options of All, Formulas, Values,
etc.

I see a Paste Special window with:

Source: C:\Documents and Settin...\Test.xls Sheet1:R1:R65536 with "Paste"
and "Paste link:" radio buttons and a "As:" window with

Microsoft Excel Worksheet object
Picture
Bitmap
etc.

I am running Excel 2002 (10.6501.6735) SP3

Again, thank you.

BG