INDIRECT Function impact on Copy Worksheet

B

BG

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
 
M

Max

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

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
 
M

Max

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

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 more:
Right-click on A1 > Paste special > Check "Formats" > OK
 
B

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
 
M

Max

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
Click to expand...

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

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

Instead of
With A1 selected,
click Edit > Paste special > check "Formats" > OK
 
Top