How to transfer a worksheet into a new workbook and keep the formulas intact

  • Thread starter Thread starter Dave K
  • Start date Start date
D

Dave K

I am attempting to build a routine for placing an existing worksheet
into a new workbook. However, i want the formulas in the existing
worksheet to be changed so that they reference the formulas in the new
workbook in which I am placing the worksheet.

Is there a best (i.e., most efficient) method for ensuring that all of
the links in the worksheet can be changed to refer to the new workbook
in which i am placing the worksheet.

Will "Edit Links" somehow allow me to change references within a
particular sheet so that alll of the formulas pull from that new
workbook?

Or do i need to resort to using the Find and Replace function,
perhaps?

To be clear, i am taking Sheet1 in January.xls and Placing Sheet1 into
another workbook called February.xls.

Thanks for any thoughts!
 
I am attempting to build a routine for placing an existing worksheet
into a new workbook. However, i want the formulas in the existing
worksheet to be changed so that they reference the formulas in the new
workbook in which I am placing the worksheet.

Is there a best (i.e., most efficient) method for ensuring that all of
the links in the worksheet can be changed to refer to the new workbook
in which i am placing the worksheet.

Will "Edit Links" somehow allow me to change references within a
particular sheet so that alll of the formulas pull from that new
workbook?

Or do i need to resort to using the Find and Replace function,
perhaps?

To be clear, i am taking Sheet1 in January.xls and Placing Sheet1 into
another workbook called February.xls.

Thanks for any thoughts!
Click to expand...

What refs are being used by the sheet that aren't local to the sheet?
When you 'Copy' a sheet that uses global refs (workbook level) to
another workbook that also uses those same refs, Excel asks which you
want to use. Answering 'YES' breaks any links to the original workbook
so the sheet uses the refs in the workbook being copied to.

This, incidently, is why it is considered 'best practice' that use of
global scope for refs/names should be limited to "only when absolutely
necessary". Otherwise, local scope (sheet level) refs/names should be
used by default. This way, copying sheets to another workbook is safe
and raises no conflicts.
 
What refs are being used by the sheet that aren't local to the sheet?
When you 'Copy' a sheet that uses global refs (workbook level) to
another workbook that also uses those same refs, Excel asks which you
want to use. Answering 'YES' breaks any links to the original workbook
so the sheet uses the refs in the workbook being copied to.

This, incidently, is why it is considered 'best practice' that use of
global scope for refs/names should be limited to "only when absolutely
necessary". Otherwise, local scope (sheet level) refs/names should be
used by default. This way, copying sheets to another workbook is safe
and raises no conflicts.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks for the reply.

What exactly do i need to do to ensure that I am using local
references?

I just tried copying into the new workbook, and all of the formulas
refer to the old workbook, so is there something else I should do to
avoid that from happening?

(FYI, it looks like i can just Find the new workbook name in the new
workbook, and then replace it with a blank as one option)
 
Dave K wrote on 4/18/2011 :
Thanks for the reply.

What exactly do i need to do to ensure that I am using local
references?

I just tried copying into the new workbook, and all of the formulas
refer to the old workbook, so is there something else I should do to
avoid that from happening?

(FYI, it looks like i can just Find the new workbook name in the new
workbook, and then replace it with a blank as one option)
Click to expand...

If your sheet being copied to another workbook contains formulas that
ref other sheets in the original workbook then you're going to have
calculation problems if you break those links. It's hard to say what to
do without seeing what you've got. Any chance you can post back and
attach the files so I can look see?
 
Dave K wrote on 4/18/2011 :

If your sheet being copied to another workbook contains formulas that
ref other sheets in the original workbook then you're going to have
calculation problems if you break those links. It's hard to say what to
do without seeing what you've got. Any chance you can post back and
attach the files so I can look see?
Click to expand...


Best perhaps to copy the entire workbook, and then edit the copy?
 
Best perhaps to copy the entire workbook, and then edit the copy?
Click to expand...

It seems to me that you might be using this sheet as a template that
gets inserted into other workbooks. In this case I'd take a different
approach, but I'd still like to see real examples of what you're trying
to do before making any suggestions.
 
Back
Top