Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win



Posted on 2011-02-15
Medium Priority
Last Modified: 2012-05-11
hi i'm using excel 2003 and i need to copy a worksheet to the end of the same workbook, but when i right click and select "move or copy", then select "move to end" and check the box "create a copy" it wont copy over. I have checked that the right workbook has been selected but still wont work, please assist.
Question by:Frank .S
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 3
LVL 50
ID: 34904207

in the first box of the "Move or Copy" dialog, make sure that the current workbook is selected. It is possible to copy a sheet to a different or a new workbook. The current workbook is the default, though.

Also, how many sheets are in the workbook? Do all sheet tabs show? Maybe you need to scroll to the rightmost tab to see the newly copied one.

cheers, teylyn

Author Comment

by:Frank .S
ID: 34904219
hi teylyn, yes have checked that the correct workbook is selected, and there are only 5 worksheets in this workbook and yes i can see all 5, just cant add a copy of the one i want.
LVL 50
ID: 34904240
Hmm, works fine in my Excel 2003. Sorry, I'm stumped.

I was thinking Workbook protection, but with that enabled, you would not be able to even open the dialog.

Can you try this:

make a copy of the file
in the copy, delete all contents in all sheets
enter some text into A1 of the sheet you want to copy
try to copy the sheet
If that does not work, post the empty workbook up here.

If it DOES work without content, then we need to research into what aspect of the content could be preventing the copy.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 34904250
I know it will not make any sense, but it's worth to try.

1) Press Alt+F11 (to open the VBE window)
2) Close the VBE window
3) Try to copy the sheet again, exactly as you're already trying.

if it works, then I'll explain why. :-)
LVL 50
ID: 34904260

>>if it works, then I'll explain why. :-)

This makes me curious. Care to share what the connection may be even if it does not solve this question?

cheers, teylyn

Expert Comment

ID: 34904291
if you try to add a new sheet without having VBA opened, BY DEFAULT all versions of Excel create a new sheet without a codename. This may affect VBA code that depends on the codename, as this property will be empty.
Now, I dont think this has anything to do with FrankSasso's problem, BUT since it's a known bug in Excel, I thought we should give it a try.

To test what I'm saying, I have a workbook at work with a bunch of code, but the simplest way is to put this VBA inside the ThisWorkbook code page. Save it, close it (no necessary to close it though)
Then, with the Excel only (no VBE), insert a new sheet. You'll see a blank message box.
Then do Alt+F11, and go back to Excel, and try again to insert a new sheet, either by selecting insert and/or Copy...

After 12 years of experience with Excel, I was never able to fix the problem.
1) flasdhing the VBE using sendkeys works, but sometimes VBE doesn't close
2) try to write code to guess what the codename should be doesn't work, because there's no codename until you open the VBE... once you open it, it gets automatically created, to populate correctly the Project Explorer window
3) Using the name of the sheet is never safe, because sometimes the name does not match the codename, regarding the number...
4) maybe I'll remember more thing I tried...

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    MsgBox Sh.CodeName
End Sub

Open in new window

LVL 50
ID: 34904331
Fernando, I've followed your instructions. I see the blank message box when the VBE is closed. I can also copy an existing sheet with no errors and no message box appearing.

When the VBE is open, I can copy an existing sheet, too, with no errors, and when inserting a new sheet, the message box shows the sheet number.

It's an interesting phenomenon (with the blank message box), but I'm not sure that this is related to the underlying issue of FrankSasso's question.

cheers, teylyn

Expert Comment

ID: 34904341
thats exactly what I said :)
I'm also not sure, but since his problem is also very unusual, i thought that maybe there could be a relation.

cheers :)
LVL 50
ID: 34904347
True, it was worth a try. :)

Expert Comment

ID: 34904349
i actually thought that maybe the fact that the sheet has no codename, would make it "uncopiable"...
But you already ruled out this possibility... anyway, let him try ! :-) you never know !

Author Comment

by:Frank .S
ID: 34910961
Hi I have tried your directions but still will not copy any of the worksheets. I have now attached a copy of the workbook for you to investigate further.

Accepted Solution

FernandoFernandes earned 1400 total points
ID: 34911265
My Excel 2003 failed to perform the copy of first sheet to the end...
BUT my Excel 2007 and 2010 did it fine.
I checked protection, file is not protected at all
I checked compatibility (using 2010), and nothing shows up there, that could mean anything
I checked the VBE, there's no macros stopping the process
I tried moving the first sheet with the mouse, like drag and drop, but holding the Ctrl, also didn't work

still playing with it... but you have a ghost here, my best suggestion if you want to be able to work on the file, create a brend new file from scratch, with all the contents of your file... and work on the new one...

I did it for you in the attached file and it's working now.
unfortunately this doesn't tell us what exactly is wrong with your file though.


Author Comment

by:Frank .S
ID: 34911338
what is the quickest way to copy all my worksheets to a new workbook because i dont want to lose any of the formatting and values.
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 600 total points
ID: 34911360
Sometimes a file becomes corrupt. In that case it may be possible to repair it with these steps:

Save it as an HTML file.
Close Excel
Open Excel
Open the HTML file
Save as Excel file (with a different name than the original)
Close Excel
Open Excel.

It does not work with all kinds of corruption, though, so may or may not work in your case.

cheers, teylyn

Expert Comment

ID: 34911367
if you want to send me your workbook with all the content, I can do it for you, I made it quick because I have a script which I wrote some time ago...

you can also give it a try too... but it's easier if I do it for you ...

The "replicator" is attached....

Expert Comment

ID: 34911412
teylyn, I've done this thing that teylyn suggested before, it's a good approach to when you're having too many custom formats... I also started using ASAP Utilities for removing unused styles and also started saving as XLSX (or XLSM), and then saving back as XLS... this solves a lot of things...

I forgot to test these approaches with FrankSasso's file though !!

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question