0

So my issue is that I have 2 excel workbooks, both with formulas & some VBA. I need to merged them into one.

The VBA part is easy to handle with the modules.

However, I DON'T want to have to redo all the references from the formulas. If I just copy the worksheets, then Excel will have the references to the source workbook, which I'll have to edit and that's a lot to edit (and potentially mess up).

Formulas are using all named ranges, and it's easy to make it so that all the named ranges used in each actually exist in the merged workbook. Also, there are no conflicts in existing named ranges.

I tried to copy the worksheets, then use "Edit Links" under Data to change the source. But that doesn't work (I get and endless suit of pop-pup windows telling me there's a problem with one of the formula... apparantely you can't change the source to the workbook itself).

Any smart ideas or do I have to brute-force this cell by cell?

To be clear: I'm not looking for a macro this is a one-off copy. Just want to handle the formula changes smartly.

logicOnAbstractions
  • 2,178
  • 4
  • 25
  • 37
  • Would find and replace work? – Mark Fitzgerald Jun 21 '16 at 11:20
  • Let me know if this helps: http://stackoverflow.com/questions/37791038/how-do-i-keep-cell-referencing-in-excel-if-i-replace-sheet/37792270#37792270 – Ralph Jun 21 '16 at 11:29
  • Depends in which sense you mean Mark - I have the feeling your initial answers was to F&R every reference to the old workbook. I don't like the possibilty that some silly twist & turn in one of the formulas creates a bug that may stay hidden for a while.... However, you gave me a new & I think better idea: In the ORIGINAL workbook, F&R every "=" in the formulas. Move the sheets. Break all links that has created in new workbook. Then put the "=" sign again. I think this less error-prone? But then some formulas will have if(xxxx,"="xyz"....)... – logicOnAbstractions Jun 21 '16 at 12:06
  • Though perhaps I can just remove the = if there's nothing ahead of it. – logicOnAbstractions Jun 21 '16 at 12:07

1 Answers1

0

Francky_V ,

I have done this multiple times when "cleaning up" worksheets that I have inherited from other users. An easy way to fix all of the reference formulas at once is to use the "Show Formulas' from the Formulas tab on the ribbon. If I had a 'sheet1' reference, I would do a Find & Replace 'sheet1' with 'sheet(whatever)'. you can do each of these one by one or all at once. If you strategically, F&R, you will be consolidated in no time. Additionally, you can use the following code to identify which cells have a formula in case you choose the brute force method for some.

Sub FindFormulas()
Range("A1:XFD1048576").SpecialCells(xlCellTypeFormulas).Select
End Sub
E.Pyles
  • 9
  • 5