"Dumb copy"

  • Thread starter Thread starter sdm
  • Start date Start date
S

sdm

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
 
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
 
Adjust to your own workbook/worksheet names:

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

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
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
 
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
 
Back
Top