This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I am copying a worksheet that has formulas in some ranges to a new workbook. The formulas are VLOOKUPS that reference another worksheet in the source workbook. When I look at the formula in the new workbook (the destination workbook) it has embedded in the formula the path to the source workbook.
The name of my source workbook is DP_Data_Capture.xls
Its full path is 'C:\Projects\DP_Data_Capture\development[DP_Data_Capture.xls
This is my copy worksheet statement...
ws.Copy After:=Workbooks(wbdestination.Name).Sheets(wbdestination.Sheets.Count)
where ws is my source worksheet and wbdestination is my destination workbook
This is an example of the formula in the source worksheet...
=IF(AP14="","",VLOOKUP(AP14,CostCtr!$B$1:$N$4236,13,FALSE))
CostCtr is a worksheet in the source workbook, I also copied it to the destination workbook.
when I look at the formula in the destination workbook it has the path to the source workbook in it...
=IF(AP14="","",VLOOKUP(AP14,'C:\Projects\DP_Data_Capture\development[DP_Data_Capture.xls]CostCtr'!$B$1:$N$4236,13,FALSE))
I need it to look like this...
=IF(AP14="","",VLOOKUP(AP14,CostCtr!$B$1:$N$4236,13,FALSE))
exactly like the original
Any help is greatly appreciated!
Thanks
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
As a follow up I see if you go into Menu EDIT..LINKS and select CHANGE STATUS you can remove the link.
However is there a way to do this using vba during the copy process.
I really don't want the users having to do this.
Thanks