2

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.

2
  • 2
    Paste special works even if you copy data on a new spreadsheet, are you sure that you actually copying the data? Dec 10, 2012 at 13:31
  • Sorry, I probably didn't explain myself quite well enough. What I meant is that whereas if I copy a section of my spreadsheet and then right click elsewhere in the same spreadsheet and select "Paste Special", I get the "Paste Special" dialogue box open up where I can select from the radio buttons what I want to paste; everything, or individually select whatever I want from the list, i.e. just formulas, values, formats, comments etc. This option doesn't exist though when copying a group of cells from one spreadsheet to another brand new one as all i get is the option to select the file type.
    – Bruce
    Dec 10, 2012 at 14:07

3 Answers 3

2

Sounds like you're trying to copy data from one Excel "instance" to another. Are you clicking the Excel application from the start menu to open up a new workbook?

When you open Excel in a new instance and try copying from one instance and paste special into another, I get this dialogue:

enter image description here

Is this what you're getting?

Try this:

  1. Open your workbook to copy
  2. In Excel, click File (Or Office Button), New -> Create new blank workbook
  3. Copy your data from the original workbook
  4. Go to the new workbook (in the same Excel instance) and right-click -> Paste Special

The menu should come up correctly

2
  • Thank you Joseph. Yes, that was the dialogue box I was getting as i was trying to copy selected cells from one spreadsheet I've used for a long time to another one I've recently created this past week. The formulas and comments don't then get copied across for some reason. But when I did what you stated and clicked to create a brand new workbook and then clicked Paste Special, lo and behold the normal "Paste Special" dialogue box opens up giving the option to select, All, or just values or formats or comments etc. I've no idea why that works though and doing it the other way doesn't. Thank you
    – Bruce
    Dec 10, 2012 at 14:36
  • @Bruce, I'm not exactly sure why it doesn't work when you have two instances. It might have something to do with the fact that MS Office has their own Clipboard and I guess when you try to paste to another application instance, it has to use the Windows clipboard? either way, I'm glad it's working now for you!
    – Joseph
    Dec 10, 2012 at 14:39
1

To move a worksheet from one existing instance (workbook) to a different instance (workbook), both instances must be open concurrently. Note: *A worksheet can be moved to a new instance by selecting "New book" - when that option is available. Formulas will stay intact when the worksheet is moved or copied. Here's how:

  1. Right click the worksheet to be moved/copied. A drop-down menu will appear.
  2. Select "Move or Copy". A dialog box, entitled Move or Copy, will appear. Then, below and to the right of...

    • Move the selected sheets
    • To book:
  3. , Click the upside down triangle, which will produce a drop-down menu of instances currently open. This list of instances will include "(new book)", and any workbooks that are open. *Only one workbook can be selected during a transaction.

  4. Finally, select the workbook/instance to which the worksheet is to be moved. Click OK. The worksheet will be in the target workbook.

If a copy is to exist in the original and target locations, select Create a Copy", before clicking OK.

0

I don't know why you are not able to paste special into a new workbook, but since this is the case, you could try copying the entire sheet to a new Excel file and then getting rid of any extra information that you don't want to have. To do this:

  1. Right-click on the sheet you want to copy
  2. Select "Move or copy..."
  3. Click on the dropdown menu and select "(new book)"

This way you should have a brand new Excel file with all the old data.

4
  • Thanks for that, I've tried it and it works, retaining all of the formatting, comments, everything. However, if anyone knows how you can do the same thing but selecting just part of the spreadsheet to copy, that would be really useful to know as it would save much time in deleting/modifying all the stuff I don't want to copy across. Thanks for your help though; it's really appreciated.
    – Bruce
    Dec 10, 2012 at 14:23
  • You could simply select some of the columns to copy, move them to a new sheet inside the same workbook, and then create the new book in the way I explained in my answer. Dec 10, 2012 at 14:28
  • Useful info too. Thanks user1301428. This is the first time I've used this website and it's been really helpful and the answers far quicker in coming than expected. Thank you.
    – Bruce
    Dec 10, 2012 at 14:38
  • You're welcome :) Now you can award me some points :P Dec 10, 2012 at 14:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .