Written by Allen Wyatt (last updated March 22, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021
Neil has Excel installed on several PCs in his office. On most of these he can copy data from one workbook to another workbook with no problems. On one of the PCs, although he can copy data from one worksheet to another worksheet in the same workbook, Neil cannot copy data to a different workbook.
More than likely this has to do with how the two workbooks are opened on the problem machine. If you open the workbooks in two different instances of Excel, then copying and pasting between instances is not the same as when the workbooks are opened in the same instance. If you open the two workbooks in different instances of Excel, then when you go to paste information into the target workbook, you get the results of whatever formulas you are copying instead of the original formulas. (This is just one example; there are other pasting differences as well.)
The solution is to make sure that the workbooks are opened in the same instance of Excel. The easiest way to do this is to make sure that once the first workbook is open, you open the second workbook by using the Open dialog box within Excel. If you, instead, use the Windows Start menu or a Desktop icon to open the second workbook, you are opening a second instance of the program.
If this doesn't solve the problem for you, then you should make sure that there is no macro running in the target workbook or worksheet when it is activated. Look for code in the ThisWorkbook module and any other worksheet module in the target workbook's VBA project. If you do find a macro there, then it could be that the macro is altering what is in the Clipboard or clearing it out entirely. You'll need to do some detective work to figure out if this is the case.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8034) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021. You can find a version of this tip for the older menu interface of Excel here: Can't Copy Data between Workbooks.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Do you want to limit what can be entered into a particular cell in your worksheet? Here are three separate ways you can ...
Discover MoreIf you distribute a workbook that is used by others for data entry, you may want a way to make sure they fill in certain ...
Discover MoreNeed to concatenate the contents in a number of columns so that it appears in a single column? Excel has no intrinsic way ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2023-03-23 04:11:48
Kiwerry
Some off-the-cuff comments on Philip Andrew's solution which may be useful:
The method described will work quite happily without the detour through Word: Click on a cell then get into edit mode by clicking in the formula bar or hitting F2. Mark all of the text and copy it (for example, Ctrl-A then Ctrl-C). Press Enter to leave the cell, change to the workbook where you want to use the formula, click on the target cell and get into edit mode, then paste and press enter. The formula should be there and work as it did in the source cell.
Caveats:
References to other cells are NOT automatically changed as they would be if you were copying and pasting within a single Excel instance. Either check the references carefully before using the formula in the target workbook or make sure that the source cell and the target cell have the same address (for example, if you copy something like =A25+B25 from cell C25 and paste it into any cell other than C25 in the target workbook, the results may be different from what you expect because the formula still says =A25+B25 and doesn't refer to the neighbouring cells).
You are only copying the formula; formatting will not be copied.
You can only copy one formula at a time.
The method can be useful, despite the caveats.
2020-08-22 15:53:35
Philip Andrew
I might have discovered another workaround for copy-pasting a formula from one wb to another. I copied the source formula, hit Enter to close the cell I just copied from, then pasted the formula into a new blank MS Word document. I then did a Ctrl-A in the Word doc to select all the text I just pasted there, then hit the Clear All Formatting icon in my QAT, copied the still selected format-clear formula text in Word, and pasted it into the cell in the target wb. Presto, It worked!
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments