Create clone copy of spreadsheet for a new year

[DarCady]

I have an Excel spreadsheet containing a large amount of formulas applicable to 2009. I want to create a duplicate workbook for 2010. I want to maintain all cell formatting, ie A1=sum(whatever) without retyping it all. When I attempt to use copy-and-paste (or paste special) from 2009 to 2010, the cell values maintain a reference to the 2009 workbook. For example on 2010 workbook, A1=[2009]sum(whatever). Therefore the values from 2009 print in cells. I only want to maintain cell formula formatting, not a reference back to 2009 workbook. How do I do this? I hope I have been clear.

[modytrane]

I think you are trying to copy formulas from one workbook to another.
Not from one worksheet to another within the same workbook.

The simplest thing you can do is save your 2009 workbook with a different name [say, 2010....].

Then in this new 2010 workbook all your formulas and formats are same.
If your formulas have 2009 in them, you can go to edit>replace all "2009" with "2010".

Unless I am missing something, this should do it.

modytrane

[DarCady]

This may work and I will try that when I am done working for the evening. But to clarify further, the result I would like to see is the same formatting from all of my 2009 workbooks (I have corrected earlier syntax error mentioned by responder), with all of the same underlying formulas into a new 2010 workbook that will have zero value until I enter something in the appropriate box. I want 2010 to be a blank slate (data values) but an exact copy of 2009 formatting and formulas; also maintaining the 2009 workbook completely. Will this work or does this further clarify into a different answer? BTW, thank you for your previous reply and understand that all of the emphasis in this second post is just for clarity's sake.

[NBVC]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[modytrane]

I think I understand exactly what you are trying to do.
I have done the same thing for a couple of my workbooks.
I would use 2009 workbook, then "save as" with a new name for 2010.
This will leave the original 2009 book in tact with all the data.
The new book has the same data as 2009.
So, you can go in and highlight all the input cells and clear the values.
All the formulas will re-calculate and results would go to zeros or blanks depepnding on how the formulas are written. I write all my formulas to show blank when there is no input data or input value is zero.
As a matter of preference I always create one worksheet as data entry sheet and other worksheets to show reports, calculated summaries etc.
This makes it easier for future and allows you to customize formating for reports.
If the formulas have year 2009 in them, then as I mentioned earleir, you can got to edit>replace and replace all 2009 with 2010.
If you have a clean workbook at that point, you can save a copy as master for future years, so you don't have to go through it next year.
If you need any help or have further questions, please post them here and someone will help you.
Good Luck,
modytrane

[DarCady]

Thank you so very much! That worked perfectly!