Changing worksheet references

  • Thread starter Thread starter Ken Warthen
  • Start date Start date
K

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
 
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.
 
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''.


Luke M said:
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!*


Ken Warthen said:
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
Click to expand...
Click to expand...
 
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!*


Luke M said:
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!*


Ken Warthen said:
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
Click to expand...
Click to expand...
 
Back
Top