Solved

EXCEL WORKSHEET - WORKSHEETS

Posted on 2011-02-15
16
404 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:FrankSasso
  • 7
  • 6
  • 3
16 Comments
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
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:FrankSasso
Comment Utility
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

Expert Comment

by:teylyn
Comment Utility
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
 
LVL 6

Expert Comment

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

Expert Comment

by:teylyn
Comment Utility
@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
Comment Utility
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

Expert Comment

by:teylyn
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 50

Expert Comment

by:teylyn
Comment Utility
True, it was worth a try. :)
0
 
LVL 6

Expert Comment

by:FernandoFernandes
Comment Utility
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:FrankSasso
Comment Utility
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 350 total points
Comment Utility
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:FrankSasso
Comment Utility
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:teylyn
teylyn earned 150 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now