Copy Cells and Formulas from One Workbook to Another

My supervisor is constantly making changes to some rather complicated spreadsheets we use. He sends these to me in the form of a duplicate spreadsheet and then tasks me with getting the new cells copied into the working copies of the spreadsheets that are used by the entire company. Why...because if something goes wrong he can blame me and be in the clear himself. Oh well, I knew this job was dangerous when I took it.

Many of the cells he adds or changes have complex engineering formulas in them which reference other cells within the same workbook. So far, he hasn't gone out and strarted referencing cells in other workbooks, its all contained in the same workbook file. When I want to copy what he did in this separate workbook it would be nice just to do a copy and paste. But Excel wants to add the path and filename of the workbook I copied from when I paste it into the formal copy on the servers, which is no good. To date, I have been painstakingly copying the cells with formulas into something like Notepad and then copy and paste them into the respective cells in the formal spreadsheet. This works but it can get messy and sometimes I get interuppted and have to start all over again.

Is there a way to copy cells from one workbook to another without bringing in all the pathing data from the other spreadsheet? I want it to end up like I simple copied it from within the same workbook, not from another source file.

It depends on how many formulae you have to copy.

If you add a space at the beginning of each formula, it will make it text. You can then copy and paste them into the other workbook. Then simply remove the space from each text formula to convert it back to a real formula.

If you have a lot, you might need a macro to do it.

But give it a go and see if it works for you first.


Regards, TMS