I cannot copy worksheet

duxis

New Member
Joined
Jul 18, 2007
Messages
7
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!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

duxis

New Member
Joined
Jul 18, 2007
Messages
7
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.
 
Upvote 0

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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.
 
Upvote 0

duxis

New Member
Joined
Jul 18, 2007
Messages
7
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?
 
Upvote 0

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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:
Code:
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:
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,191,707
Messages
5,988,230
Members
440,139
Latest member
ngaicuong2017

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top