I cannot copy worksheet

[duxis]

Hello,

I'm new here and I need very urgent help!

I've got workbook with seven sheets. I need to duplicate a sheet in a workbook, but I cannot. Nothing happens. I even cannot duplicate any sheet into a new workbook. I can create new sheet, but cannot copy existing sheet.

If I open another workbook, everything works fine. That's why I suppose it's the problem of workbook, but cannot resolve it.

I have also tried to copy the sheet in VBA, but got an runtime error 1004 telling something that copy method failed.

According to microsoft knowledge base it happens when a sheet is inserted to workbook several times and file wasn't saved. But I open a file and try to copy the sheet for the first time and still getting error.

I've tried to search for help here on this forum, but wasn't able to find an answer. My problem is so general, that I got 493 pages of search results. I took a look at first seven or eight of them, but with no success.

Please, if it is discussed somewhere, i will be thankfull for link.

HELP!

 

[xenou]

Check Tools | Protection | Protect Workbook (or Unprotect workbook)

Also, you cannot delete a sheet in a shared workbook.

Otherwise, you might try to rebuild the workbook by copying the cells only (not the sheets) as you may have some kind of corrupted file here.

Another remote possibility:
http://en.allexperts.com/q/Excel-1059/worksheets-won-t-copy-1.htm

 

[duxis]

Thanks mate for your effort, but workbook is not protected. Also not shared.

Problem in the link is exactly mine. But i already created a new workbook, copied the shhets into it, and then it worked. But, as soon as i copied also vba code and forms into this new workbook, it's not working again.

Perhaps something worng in the code, but I really can't figure it out.

 

[xenou]

Yes...sounds like the code is the problem...
You might want to try some debugging and/or posting relevant portions of code.

Generally speaking, make sure worksheets are active (Worksheets("Sheet1").Activate and/or fully qualified--Worksheets("Sheet1").Copy--and use workbook references as well if needed.

Regards.

 

[duxis]

Now I created copy of my whole workbook, exactly as you (or someone else in the link you provided) advised. I've created new file with the same number of sheets, then I copied all the cells of all the sheets to the new workbook and then I copied the vba code also.

It took me about 15 minutes, but the result was good. It worked... but only for half an hour. Now, i'm back in trouble again. File size went from 110kb (just after finishing new copy of workbook) to 500kb after few changes in formulas and removing some code in VBA. Also, file opens very slowly now (exaclty as it did with the oroginal file).

I'm affraid it cannot be caused by code, becasue it doesn't work from the very opening of file, when no code was run at all (except for workbook open procedure, which creates new menu item with two commands).
Here it is:

Sub CreateReportMenu()
Dim NewMenu As CommandBarPopup
Call DeleteReportMenu
Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
If HelpMenu Is Nothing Then
Set NewMenu = CommandBars(1).Controls.Add _
(Type:=msoControlPopup, temporary:=True)
Else
Set NewMenu = CommandBars(1).Controls.Add _
(Type:=msoControlPopup, Before:=HelpMenu.Index, temporary:=True)
End If
NewMenu.Caption = "&Reporting"
Set MenuItem = NewMenu.Controls.Add(Type:=msoControlButton)
With MenuItem
.Caption = "&Daily Footfall"
.FaceId = 52
.OnAction = "FootfallDialog"
.BeginGroup = True
End With
Set MenuItem = NewMenu.Controls.Add(Type:=msoControlButton)
With MenuItem
.Caption = "&Monthly Footfall"
.FaceId = 52
.OnAction = "TurnoverReportExternalDialog"
End With
End Sub

Sub DeleteReportMenu()
On Error Resume Next
CommandBars(1).Controls("Reporting").Delete
End Sub

Any sugestion?

 

[xenou]

I am out of ideas for you. Copying sheets is a very ordinary task, whether in native Excel (by right-clicking the sheet tab and selecting copy) or in VBA using the copy method. Either something is unusual about procedure you are following (not yet identified) or something is terribly wrong with your Excel application.

Are you saying that you simply cannot use:

Sub test()

'Copy Sheet1
Worksheets("Sheet1").Cells.Copy

'Paste on Sheet2
With Worksheets("Sheet2")
    .Activate
    .Range("A1").Activate
    .Paste
End With

Application.CutCopyMode = False

End Sub

Or:

Sub test2()

Worksheets("Sheet1").Copy Before:=Sheet1

End Sub

And:
You cannot right click on a sheet tab and select "copy" from the right click menu, and you cannot copy cells from one sheet to another?

Regards.