Copying formulas between workbooks

**Vic_from_sweden** · 10-31-2012, 08:52 AM

Hello,

I have a workbook (lets call it WB1) with two sheets. In Sheet X I have some formulas that refers to cells in Sheet B.

I want to copy the entire Sheet A to another workbook (called WB2). In the new workbook I want the fomulas to link to cells in Sheet B for WB2.

But if I copy the formulas from WB1, Sheet X and paste them in a new sheet in WB2 the all refer back to WB1.

Thanks in advance

Vic

**martindwilson** · 10-31-2012, 09:06 AM

use move or copy right click the tab ,select move or copy,chose copy then either copy to any other opened workbook or select new work book

**Vic_from_sweden** · 10-31-2012, 09:18 AM

But if I do that, the formula in the first workbook, that there is for example =SheetB!A1*7 and that I want to say:

=SheetB!A1*7[/I]

instead says

=[Book2]SheetB!A1*7

Can I copy so that [Book2] doesn't appear?

**martindwilson** · 10-31-2012, 09:23 AM

it doesnt do that when you choose make a copy

**Vic_from_sweden** · 10-31-2012, 09:35 AM

(before I begin, Book1 only has one sheet, Sheet2)

In Book1.xlsx I right-clicked on Sheet1 and chose "Move or Copy"
I then clicked "Create a copy" and chose Book2 and placed before Sheet2
The formula that was =C2*Sheet2!A1
then appears in Book2 as =C2*[Book1]Sheet2!A1

**yosh99** · 10-31-2012, 12:55 PM

After you copy into Book2, select the entire sheet in Book2 (or all the cells that are referencing Book1)
click CTRL-H
enter [Book1] in the "Find What" box and leave the "Replace with" box blank
click Replace All

**martindwilson** · 10-31-2012, 02:30 PM

i see noe..if the sheets are simply sheet1!,sheet2! it works fine,if the sheet referenced has another name it does the {workbook} trick,so using find replace as suggested should work