Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
The easiest way to copy a sheet of formulas! (Excel 2007)
Hi everyone! After many hours of fiddling with Excel 2007,
I believe that I have finally discovered the easiest way to copy a full sheet of formulas between workbooks which eliminates references to the source workbook. Several people here have suggested solutions which involve the use VBA code or copying each individual formula from the formula bar and pasting them into the destination sheet's cells. I believe that I found an easier way (with Excel 2007) Suppose you have two workbooks: "formulas.xlsx" and "dest.xlsx". 1) Go to "formulas.xlsx", find the sheet that contains all your formulas. Highlight or select the entire range/area that contains the formulas. 2) Go to the "dest.xlsx" workbook and do a normal paste operation. (Ctrl-V will do fine) 3) On the "dest.xlsx" workbook, go to the tab/menu titled "Data". You should see a menu/button option titled "Edit Links". Press "Edit Links" 4) Press "Change Source". In the file select dialog box, select "dest.xlsx" That's it! Your done! As a side note, the above steps will NOT work if the "formulas.xlsx" contains sheet names or references that do not exist in the destination sheet. For example, if one formula is "=Sum(A1:MySheet)", and "MySheet" does not exist in "dest.xlsx", then the above procedure will fail. Hope that helps everyone! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
The easiest way to copy a sheet of formulas! (Excel 2007)
That is one way.
I generally just do an editreplace in source. Select the formulas. EditReplace What: = With: ^^^ Replace all. Copy to new workbook. Reverse the editreplace. Close source wb without saving. Gord Dibben MS Excel MVP On Thu, 17 Dec 2009 12:01:26 -0700, "Robert Crandal" wrote: Hi everyone! After many hours of fiddling with Excel 2007, I believe that I have finally discovered the easiest way to copy a full sheet of formulas between workbooks which eliminates references to the source workbook. Several people here have suggested solutions which involve the use VBA code or copying each individual formula from the formula bar and pasting them into the destination sheet's cells. I believe that I found an easier way (with Excel 2007) Suppose you have two workbooks: "formulas.xlsx" and "dest.xlsx". 1) Go to "formulas.xlsx", find the sheet that contains all your formulas. Highlight or select the entire range/area that contains the formulas. 2) Go to the "dest.xlsx" workbook and do a normal paste operation. (Ctrl-V will do fine) 3) On the "dest.xlsx" workbook, go to the tab/menu titled "Data". You should see a menu/button option titled "Edit Links". Press "Edit Links" 4) Press "Change Source". In the file select dialog box, select "dest.xlsx" That's it! Your done! As a side note, the above steps will NOT work if the "formulas.xlsx" contains sheet names or references that do not exist in the destination sheet. For example, if one formula is "=Sum(A1:MySheet)", and "MySheet" does not exist in "dest.xlsx", then the above procedure will fail. Hope that helps everyone! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy excel spread sheet & graph to word doc without formulas | Excel Worksheet Functions | |||
Copy an excel sheet from one file to another including formulas | Excel Discussion (Misc queries) | |||
Copying an Excel sheet keeping the formulas in the copy linked to | Excel Discussion (Misc queries) | |||
Copy sheet problem in Excel 2007 | Excel Programming | |||
Excel macro script to copy only formulas and not values from one sheet to another | Excel Programming |