I hope someone can help me as I've wasted so much time searching for an answer to this problem!

I have a large Excel 2007 spreadsheet (macro enabled) that I use as my main spreadsheet for calculating rents to tenants but have run into problems with it suddenly growing in size massively and inexplicably. I think the problem may lie somehow in my use of macros to speed up the production of monthly rent statements, but this is causing the spreadsheet to slow down in use, so before it stops working entirely!

I've started reproducing a copy of it from scratch in a new excel spreadsheet. However, I've already run into the first problem I can't find an answer to inasmuch when I try to copy a portion of my master worksheet containing all my tenant names and the cells containing their monthly, quarterly and yearly rent totals including formulas into the new spreadsheet, all that copies across are the figures and the formatting with the formulas not copied across neither the comments I have inside a number of cells. I understand that if I was copying cells within the same spreadsheet I would select "Paste Special" from the Clipboard menu but this option is greyed out when copying a selection of cells into a brand new spreadsheet.

My question therefore is how do I copy everything across from the selected cells in the old spreadsheet to a brand new worksheet inside a new macro-enabled Excel 2007 spreadsheet, to include all comments, formatting, formulas and numbers? Is this possible to avoid the onerous task of manually going through hundreds of cells with figures in, inserting the original formulas into each one? Thanks for any help you can provide.