Hi;

This may be a solve in code or without code.. (i can read the code fro
the macro generator then.).

The situation is as follows; Excel 2000.
-
File1
Sheet1 = Sheet1
Sheet2 = Test
Sheet3 = Sheet3

Test!b1 = 10
Sheet1!b1 = =Test!b1


File2
Sheet1 = Sheet1
Sheet2 = Test
Sheet3 = Sheet3
-
Now.. I copy [File1]Sheet1!b1
I go to [File2]Sheet1!b1 and I paste.

I now get:
=[File1]test!B1 as a result in that cell.
I DO NOT WANT THIS. I ONLY WANT: =TEST!B

How do I prevent excel from being too smart and copying filenames i
forumale references? I'll have to put it in code.. but click thourg
instructions will do too. Thanks

sd
-----------------------------------------------------------------------
Posted via http://www.mcse.m
-----------------------------------------------------------------------
View this thread: http://www.mcse.ms/message381926.htm

Hi,

have you tried copying test!b1.value?

it will only copy the value you have rather than the
formulae.

Rob

References to workbook and worksheets are always absolute so maybe
copy/paste isn't a good answer. You can set one cell's formula equal to
another:

Workbooks("Book1.xls").Worksheets("Sheet1").Range( "B1").Formula = _
Workbooks("Book2.xls").Worksheets("Sheet1").Range( "B1").Formula


--
Jim Rech
Excel MVP

Adjust to your own workbook/worksheet names:

workbooks("book3").Sheets(1).cells(1,1).formular1c 1= _
workbooks("book4").Sheets(1).cells(1,1).formular1c 1

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , says...

Hi;

This may be a solve in code or without code.. (i can read the code from
the macro generator then.).

The situation is as follows; Excel 2000.
-
File1
Sheet1 = Sheet1
Sheet2 = Test
Sheet3 = Sheet3

Test!b1 = 10
Sheet1!b1 = =Test!b1


File2
Sheet1 = Sheet1
Sheet2 = Test
Sheet3 = Sheet3
-
Now.. I copy [File1]Sheet1!b1
I go to [File2]Sheet1!b1 and I paste.

I now get:
=[File1]test!B1 as a result in that cell.
I DO NOT WANT THIS. I ONLY WANT: =TEST!B1

How do I prevent excel from being too smart and copying filenames in
forumale references? I'll have to put it in code.. but click thourgh
instructions will do too. Thanks!


sdm
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message381926.html

Hmm.. I'm afraid the situation is a little more complex.

The top of the sheet consists out of data which is taken from
database. This is refeshed every day.

The bottom part of the excel sheet is a range() copy from a histori
sheet to the new sheet.

(file: [Fin001.xls] and historic file [040211Fin001.xls])

It is a macro which copies this data from some 500 files..

Sometimes users make referenses to other sheets within their workboo
when adding notes. When the macro copies the historic information,
get the error I described earlier.

The fields point at an external-file cell, instead of the cell on th
other worksheet.

(For an easy example: I copy rows 50-180 from sheet 1 and 2 from th
historic workbook (with notes) to the workbook with sheet 1 and 2 a
rows 50-180)
I do this for

sd
-----------------------------------------------------------------------
Posted via http://www.mcse.m
-----------------------------------------------------------------------
View this thread: http://www.mcse.ms/message381926.htm

hmm.. That would copy only one cell;
I have to copy a range..
Range(A60:H160) to the second sheet.

I'm trying to run some tests with it now

sd
-----------------------------------------------------------------------
Posted via http://www.mcse.m
-----------------------------------------------------------------------
View this thread: http://www.mcse.ms/message382341.htm