[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

EXCEL WORKSHEET - WORKSHEETS

Posted on 2011-02-15
16
Medium Priority
?
422 Views
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.
0
Comment
Question by:Frank .S
  • 7
  • 6
  • 3
16 Comments
 
LVL 50
ID: 34904207
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
0
 

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

cheers,
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 6

Expert Comment

by:FernandoFernandes
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. :-)
0
 
LVL 50
ID: 34904260
@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
0
 
LVL 6

Expert Comment

by:FernandoFernandes
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

0
 
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
0
 
LVL 6

Expert Comment

by:FernandoFernandes
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 :)
0
 
LVL 50
ID: 34904347
True, it was worth a try. :)
0
 
LVL 6

Expert Comment

by:FernandoFernandes
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 !
0
 

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.
Excel-Worksheet---Copy-Error-.xls
0
 
LVL 6

Accepted Solution

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

Excel-Worksheet---Copy-Error----.xls
0
 

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.
0
 
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
0
 
LVL 6

Expert Comment

by:FernandoFernandes
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....
ReplicateWorkbook.xla
0
 
LVL 6

Expert Comment

by:FernandoFernandes
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 !!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

830 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