copy worksheet to a new workbook with formulas .... but new file not point to old one

N

newToExcel

hello again,

is this even possbile. i have looked into this quite a bit and so far
have not found a solution.

I have workbook A , i copy the data from A (including formulas) and
paste the data to workbook B. A has multiple worksheets and i only
copy 2 of them to B

now the formulas in B point to the data in A even though B has all the
worksheets which are referenced to in the formulas. So, in B you get
formulas of the form : [A.xls]!variable.......

is there a way to get rid of [A.xls] ??? and i cant just copy the
values, formulas are required.

btw, i'm copying and pasting in VBA. so i need a solution in VBA as
well
 
D

Dave Peterson

You can go to that receiving workbook and change the links:
Edit|Links|Change source.

Other options...

#1. If you're recreating the worksheets, you could copy both sheets to the new
workbook via Edit|move or copy sheet.

If you're just copying a range, then this won't work. This won't work if your
formulas point to other worksheets besides the two that you are copying.

#2. Change your formulas to strings, copy the ranges, paste the ranges, and
then convert them back to formulas.

Select the range to copy in the original workbook
edit|replace
what: = (equal sign)
with: $$$$$
replace all

Then copy|paste. Since you're just pasting strings (not formulas), they won't
point back to the old workbook.

After you paste, do the opposite:

Select the pasted range
edit|replace
what: $$$$$
with: =
replace all

Do it for both ranges.

And don't forget to fix the original workbook (or close it without saving).


hello again,

is this even possbile. i have looked into this quite a bit and so far
have not found a solution.

I have workbook A , i copy the data from A (including formulas) and
paste the data to workbook B. A has multiple worksheets and i only
copy 2 of them to B

now the formulas in B point to the data in A even though B has all the
worksheets which are referenced to in the formulas. So, in B you get
formulas of the form : [A.xls]!variable.......

is there a way to get rid of [A.xls] ??? and i cant just copy the
values, formulas are required.

btw, i'm copying and pasting in VBA. so i need a solution in VBA as
well
Click to expand...
 
N

newToExcel

dave,,
thanks for the reply but i dont think any of those options will work
for me

doing Edit|Links| -> the only thing i can do here is break the link and
as sson as i do that, the formula is converted to plain text. cant have
that, i need the formulas

#1
yes i am creating the worksheets by copying both sheets but since i
need formulas in some cells, after i copy both sheets (values only) i
go back and copy the few cells with formulas

#2
changing the original worksheet is not an option either as somebody
might and will save it as is, screwing up the whole sheet.

any other ideas ???
 
D

Dave Peterson

There's no option to Change Source under Edit|links???

What version of exce are you using?

#2. Can you make the original workbook read-only so that it can't be
overwritten (without some additional work)????
 
G

goober

Have you tried selecting both of the sheets you want to copy? Open the
workbook you want them copied into. In the source workbook select the
tabs at the bottom for the sheets you want to copy, right click on the
highlighted tab, select move or copy, check the box labeled create a
copy, then select the destination workbook and click ok. This works
well for me.

PS. if this is the first time you have tried this use a backup copy so
you don't accidentally damage the original.

good luck.
 
N

newToExcel

I tired this with a dummy worksheet and this seems to work. will try it
tommorrow when i goto work.

thanks.
 
N

newToExcel

thanks a lot dave but it looks like goober's solution will get the job
done

btw, i do have the option to change source but that means i have to
save the file first and thats jus more work for me but thanks for
everything.


Dave said:
There's no option to Change Source under Edit|links???

What version of exce are you using?

#2. Can you make the original workbook read-only so that it can't be
overwritten (without some additional work)????

Dave Peterson
Click to expand...
:)
 
D

Dave Peterson

Glad goober's suggestion worked.

I wonder why it didn't work for you earlier:

Other options...

#1. If you're recreating the worksheets, you could copy both sheets to the new
workbook via Edit|move or copy sheet.
 
Top