Hi
Here is the problem: I have created a workbook for the agency where I
work. It has a serious of sheets that have the various forms we have to use
to do our job and I have distributed this to the other people and they use
it to write their reports. But New York State has made some changes on a
few of their forms, so I have to change just them. These forms (sheets)
refer to other sheets in the workbook, but when I try pasting in the new
sheet I have created, the links to the other sheets in the workbook want to
refer back to the one I did the altering in -- the book I am working on.
How do I create a sheet that I can paste in that will refer to the other
named sheets in that book -- the tab names are the same in all workbooks
that employees have.
Ross

I think you have a couple of ways to try....

1. Clean up the old form on the worksheet and just paste the new form over that
sheet--select all the cells and edit|copy followed by Edit|paste.

2. If your new form has formulas that refer to other sheets in the workbook,
you can change the formulas to strings, copy the worksheet (not just the cells)
and then convert the strings back to formulas.

in the new worksheet (in the updated workbook)
Edit|replace
what: = (equal sign)
with: $$$$$
replace all
Now, the formulas are just text.
delete the old worksheet, and move in the new worksheet

Now reverse the process:
edit|replace
what: $$$$$
with: =
replace all
The text strings are back to formulas.

If you have other sheets that refer to the new sheet, you'll want to "freeze"
their formulas, too.

3. Open the workbook to be fixed.
Save it as Temp1.xls
select the worksheet to be replaced
edit|move or copy
move to a new workbook
Save that workbook as Temp2.xls
Close those workbooks.

Rename Temp1.xls to TempOld.xls
Rename Temp2.xls to Tempextract.xls

Open the workbook that contains the worksheet that was updated.
Save it as Temp1 (in the same location)
select the replacement worksheet
edit|move or copy
move to a new workbook
Save that workbook as Temp2.xls
Close those workbooks.


Now Temp2.xls has links back to Temp1.xls.
Rename Temp1.xls to TempNew.xls
Rename TempOld.xls to Temp1.xls
Open Temp1.xls (a copy of the old workbook with that single sheet missing)
Open Temp2.xls (the new worksheet with links to Temp1.xls (woohoo!)
select the worksheet to move to temp1.xls
edit|move or copy
move to Temp1.xls
Now all the links in that updated worksheet are pointing at temp1.xls
Save that workbook as the new name.


I'd do this with copies of each workbooks--just in case. And I'd use a
dedicated folder with nothing else in it -- just in case!

The only trouble I've ever had with this kind of stuff is named ranges--and I'm
don't recall if local names became global or global names became local. But I
do recall a problem that I had to fix when I was done.

(I used this technique when one of the worksheets in a giant workbook had become
corrupted. It actually saved lots of time. (difficult to believe, huh? <vbg))




Ross wrote:

Hi
Here is the problem: I have created a workbook for the agency where I
work. It has a serious of sheets that have the various forms we have to use
to do our job and I have distributed this to the other people and they use
it to write their reports. But New York State has made some changes on a
few of their forms, so I have to change just them. These forms (sheets)
refer to other sheets in the workbook, but when I try pasting in the new
sheet I have created, the links to the other sheets in the workbook want to
refer back to the one I did the altering in -- the book I am working on.
How do I create a sheet that I can paste in that will refer to the other
named sheets in that book -- the tab names are the same in all workbooks
that employees have.
Ross

--

Dave Peterson