Merging sheets -- name range conflicts

  • Thread starter Thread starter mdesandoli
  • Start date Start date
M

mdesandoli

Due to some nasty schedule constraints, there are two of us working on
an Excel workbook. Separate copies, not shared via a network.

John works on sheets A, B, and C which have plenty of internal named
ranges and internal references, but refer to outside sheets in only a
couple of cells. Some VBA code attached to the sheets. Doesn't touch
sheets D, E & F in his copy. Let's call his version J

Mike works on sheets D, E, and F. again lots of internal references,
refers to a handful of cells in sheet A (via named ranges). VBA code
attached to the sheets. Doesn't touch sheets A, B & C in his copy.
Let's call his version M.

So, now Mike & John want to combine their sheets. A simple
cut-and-paste from one to the other is a problem since there are now
numerous conflicts in Named Ranges -- sheets A, B and C in version J
and M have lots of named ranges in common. I can't accept the
destination named ranges as a number have moved locations. I could
rename each named range as i copy in the sheet but then other sheets
will not use the revised named range location.

Any tips? any way to make this easy... or at least not impossibly
difficult?

Signed,
Waiting-to-merge
 
Untested, but I did something like this a long time ago and I recall that it
worked ok.

But do it against a copy of each workbook.

Let's call the workbooks John.xls and Mike.xls for ease of discussion.

Start with John.xls
Open it up
Select sheets A,B,C and rightclick, move to a new workbook.

Save both workbooks. JohnABC.xls and JohnDEF.xls
Each will have 3 worksheets in them.

Now do the same with Mike.xls so you have mikeABC.xls and mikeDEF.xls.

Each of the mike*.xls files have formulas with links back to each other.
Same with John*.xls files.

Close up excel (or just get out of all 4 files).

Using windows explorer,
rename JohnABC.xls to OldJohnABC.xls
then rename mikeABC.xls to JohnABC.xls

The formulas in johnDEF.xls still point at JohnABC.xls, but excel doesn't know
you changed things in explorer.

Now open both johnABC.xls and JohnDEF.xls and move the worksheets back into one
of them.

Save this new workbook as JohnNEW.xls.

Did it work or was I completely offbase?

If it worked ok, but you have to tweak some things, you may find this utility
very useful. It's Jan Karel Pieterse's (with Charles Williams and Matthew
Henson) Name Manager.

You can find it at:
NameManager.Zip from http://www.bmsltd.co.uk/mvp

Post back with your results. I'm kind of curious if it worked ok for you. (I
do remember that it helped me a lot when I did it--but I don't recall if it did
things perfectly.)
 
That's quite clever.

The scheme worked fine as long as the location of the name ranges have
not changed.

Unfortunately that is not the case for me -- some cells were moved by
one of the developers.

I'm now experimenting with local and global variables and that
excellent tool you suggested.
 
I think I've worked out an alternate approach.

let's think of mike.xls as the master file into which we want to put
John's updated A, B & C sheets.

1) Open both John.xls and mike.xls sheets
2) copy A, B & C from John.xls to mike.xls -- make sure to put them at
the end of worksheets list. this seems to make a difference.
3) So now mike.xls has sheets A, B, C, D, E, F, A(2), B(2), and C(2).
The duplicate sheets have their named ranges converted to local
variables.
4) using the Name Manager tool, we convert all the variables in A, B,
and C to local variables
5) we convert all the local variables in A(2), B(2) and C(2) to global
variables
6) delete A, B & C. rename A(2), B(2) and C(2) to A, B, C

voila
 
Back
Top