Changing worksheet references

[Ken Warthen]

After copying worksheets from one workbook into a new one the cell references
in the new workbook refer to values in the old workbook A typical cell
reference in the new workbook might look like ='C:\Some
Directory\[SomeWorkbook.xlsm]SomeWorksheet'!$AQ$4'. The correct reference in
the new workbook should be =SomeWorksheet!$AQ$4.

There must be several hundred such references in the new workbook. Is there
some way, other than manually, to correct the references to the current
workbook?

TIA,

Ken

 

[Luke M]

Make sure the new workbook is saved. Then, you shoul be able to go to Edit -
Links, and change source to the current workbook, as opposed to an external
workbook.

 

[ryguy7272]

Maybe you can select this part:
'C:\SomeDirectory\[SomeWorkbook.xlsm]

Ctrl+C. Then, Hit Ctrl+F > Replace > paste the part that you just copied
into 'Find what' and finally hit 'Replace all'.

Do this on a backup, just in case the result is not what you want.

Good luck,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Make sure the new workbook is saved. Then, you shoul be able to go to Edit -
Links, and change source to the current workbook, as opposed to an external
workbook.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

After copying worksheets from one workbook into a new one the cell references
in the new workbook refer to values in the old workbook A typical cell
reference in the new workbook might look like ='C:\Some
Directory\[SomeWorkbook.xlsm]SomeWorksheet'!$AQ$4'. The correct reference in
the new workbook should be =SomeWorksheet!$AQ$4.

There must be several hundred such references in the new workbook. Is there
some way, other than manually, to correct the references to the current
workbook?

TIA,

Ken

 

[Luke M]

The other alternative is that when you start copying worksheets over into a
new book, copy them all in one movement, not one at a time. (You can select
multiple sheets using either Shift and/or Ctrl).
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

Make sure the new workbook is saved. Then, you shoul be able to go to Edit -
Links, and change source to the current workbook, as opposed to an external
workbook.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

After copying worksheets from one workbook into a new one the cell references
in the new workbook refer to values in the old workbook A typical cell
reference in the new workbook might look like ='C:\Some
Directory\[SomeWorkbook.xlsm]SomeWorksheet'!$AQ$4'. The correct reference in
the new workbook should be =SomeWorksheet!$AQ$4.

There must be several hundred such references in the new workbook. Is there
some way, other than manually, to correct the references to the current
workbook?

TIA,

Ken