Link to home
Start Free TrialLog in
Avatar of Frank .S
Frank .SFlag for Australia

asked on

EXCEL WORKSHEET - WORKSHEETS

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.
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

Hello,

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
Avatar of Frank .S

ASKER

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.
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.

cheers,
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. :-)
@Fernando

>>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
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

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
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 :)
True, it was worth a try. :)
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 !
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.
Excel-Worksheet---Copy-Error-.xls
ASKER CERTIFIED SOLUTION
Avatar of FernandoFernandes
FernandoFernandes
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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....
ReplicateWorkbook.xla
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 !!