How to copy a sheet from one workbook to another WITHOUT referencing the original workbook name in formulas?

Question

UPDATE: I found a workaround, but not really a solution in my opinion. Instead of using "Stories!A:A" I can use "INDIRECT("Stories!A:A") which seems to work in my limited testing so far. It just adds length and complexity to my formulas.. I would like an easier way if someone knows of one.

I have multiple small spreadsheets that all have the same format and the same 3 sheets. "Stories" "2024" and "2025". In the two sheets 2024 and 2025 they reference data in the "Stories" sheet in multiple ways. One example is "=COUNTIFS(Stories!A:A,B1)". The sheets within a workbook must always reference their own sheet named "Stories" From the master workbook I try to copy the 2025 worksheet into each of the small workbooks, Region1, Region2, Region3, and so on. When I copy the sheet the new sheet now has the original filename/workbook name added into the reference: "=COUNTIFS([main.xlsx]!Stories!A:A,B1)" which then references the original sheet. But, I want the formula to reference the sheet of the same name in the current workbook, not in the master.

I have tried:

1. after the copy I can do a Find/Replace and remove the original filename, for example: Find "[main.xlsx]" replace with "" and that works fine. But, that's a manual step for each of the workbooks that I would rather not have to do.
2. I have unchecked the option in Advanced settings: "save external link values" in both the originating and destination workbook. That didn't work.
3. I have tried using a Table for the data instead of accessing the sheet directly: "StoryText[month]" But, the same thing happens--the filename is added.
4. I have tried using the "Workbook Links" change, but there is no "remove link" option. I can select the filename of the current/destination workbook, and that works, but still a lot of clicking and scrolling to the filename.

I have started to explore using a VBA routine, but that seems like overkill to create, debug, and then maintain across a 20+ workbooks.

Any other suggestions? options?

thanks,

Rick

Answer 1

Older version of excel did change the worksheet formula automatically to be the previous worksheet and not the same as the worksheet you copied.

I used it all the time. When coping worksheets it changed the formula automatically to reference the worksheet you are coping and not copy the same worksheet.

It's so frustrating when they change things and I don't know how to submit question to get correct answer. The research & replace doesn't work for me because my formulas reference the previous 2 worksheets. I copy 2 worksheets at the same time to 2 new worksheets.

Answer 2

Yes, I tried that method and it can work; it would be much nicer if it gave me an option to convert the WB Link to a local link, or remove it. To use this method I have to follow this process: 1. copy the sheet from the master to the regional workbook. this creates a WB Link back to the master; 2. Save the regional workbook and reopen it. 3. Update the WB Link to the local, or regional, filename, which actually removes the WB Link. For some reason I have to save the workbook and reopen it, or I don't see the filename in the dropdown box.

Thanks again for your help,

Rick

Answer 3

Got it. The only method I know of is to use the Workbook Links and Change Source to update the formulas to look at the new workbook. Have used this for many years. It is a few steps, but it does do all of the formulas in one process.

Answer 4

Hi Rich, thanks for the pointer. I did learn some valuable things from that article. However, it doesn't solve my problem. I am trying to get rid of workbook links that are automatically added when I copy a sheet.

thanks again,

Rick

Answer 5

Perhaps this will give some additional ideas that could work.

Create workbook links - Microsoft Support