Move or Copy sheet not working / Also VBA Run-time error ‘1004’: Copy method of Worksheet class failed

[EC1728]

Good morning everyone. I have recently run into the following problem:

I have searched the forum along with numerous other sites and can't seem to find the solution to my problem. I am currently using Excel 2016 (Microsoft Office Professional Plus).

I have a sheet named 29A-1 and I am attempting to make numerous copies of the same worksheet. I have attempted to right-click on the sheet tab, select Move or Copy, then check the Create a copy box. I have also attempted holding Ctrl then clicking on the tab and dragging it to the end. The sheet and workbook are not protected.

I have also tried the following VBA Code:

Sub CopyActiveSheet()
ActiveSheet.Copy After:=Sheets(Sheets.Count)
End Sub

The VBA code will work one time and then on the second attempt it throws the Run-time error ‘1004’: Copy method of Worksheet class failed. If I reset the code it will work again one time then error again on the second time.

Basically what I end up with is tabs that look like this:

29A-1 29A-1 (2) Sheet3 29A-1 (3) Sheet5

Sheet3 and Sheet5 are completely blank. I am getting similar results when I use the Move or Copy option and clicking Ctrl then dragging the tab. Anyone have any ideas on what the issue might be or how I can fix it.

Thank you and I appreciate any help.

 

[EC1728]

I whipped up the following code and it works for me ...

Sub DuplicateSheetMaker()
'
'   This code creates 'x' amount of duplicates of the Active sheet in excel, the new tabs are created right next to the Active sheet tab.
'
    Dim CopyNumber  As Long                                 ' This will be used to count what copy number we are at
    Dim TotalCopies As Long                                 ' This will be used to set the amount of copies of the Active sheet desired
'
    TotalCopies = 10                                        ' <--- Set this value to the amount of copied sheets that you want
'
    For CopyNumber = 1 To TotalCopies                       ' Loop for creating the copies of the Active sheet
        ActiveSheet.Copy After:=ActiveSheet                 '   Create the copies directly after the Active sheet
    Next CopyNumber                                         ' Loop back to start of loop until all desired copies are made
End Sub

The result was 10, in this example, exact copies of new sheets right next to the Active sheet.

Johnny,

Thank you for the code. However, I am still experiencing the same result. When I execute your code on the workbook I am working on it will make one copy of the active sheet then throws the Run-Time error '1004': Copy Method of Worksheet class failed

When I debug the code it highlights this line:

ActiveSheet.Copy After:=ActiveSheet

I also tried your code in a new workbook and got the same result. So I am still ending up with

29A-1, 29A-1 (2), Sheet3

Where Sheet3 is a blank sheet.

 

[EC1728]

Post #6 …​

Marc,

I do not have access to Excel 2013 or 2010. I am completing this at work and all we have is Excel 2016.

 

[Marc L]

​

So without any material to test I just can say to ask your IT to repair / reinstall Excel …​

 

[EC1728]

​

So without any material to test I just can say to ask your IT to repair / reinstall Excel …​

That might have to be my next step. I tried using a different computer and still got the same result both on my workbook and a brand new workbook.

 

[Marc L]

​

Another point : desactivate any add-in / add-on whatever as some can change the Excel behaviour …​

​

Check also on VBE side in Tools menu References option if any reference is missing so uncheck it …​

 

[EC1728]

​

Another point : desactivate any add-in / add-on whatever as some can change the Excel behaviour …​

​

Check also on VBE side in Tools menu References option if any reference is missing so uncheck it …​

I disabled all the Add-In's.

In VBE the only references checked are:

Visual Basic For Applications
Microsoft Excel 16.0 Object Library
Microsoft Office 16.0 Object Library
OLE Automation

All others are unchecked.

 

[Marc L]

​

So if it still raises an error so to see with your IT …​

 

[rlv01]

rlv01,

I ran your code both in the workbook I initially had the issue with then I ran it in a new workbook. I got the same result in both workbooks. I attached a snip of each of the results. I am at a complete loss at this point. I have put so much time into this project and without the ability to copy the sheet will make it unusable.

Any thoughts?

What happened when you used F8 to single-step the code execution?

 

[rlv01]

Though I have not seen it, there have been some reports of problems with copying too many sheets without saving the workbook, so this might be worth a try as an experiment:

Sub CopyActiveSheet()
    Dim WB As Workbook
    
    Set WB = ActiveSheet.Parent
    ActiveSheet.Copy After:=WB.Sheets(WB.Sheets.Count)
    WB.Save
End Sub

If you have a lot of formulas on the sheets you are trying to copy then another test might be:

Sub CopyActiveSheet()
    Dim WB As Workbook
    
    Set WB = ActiveSheet.Parent
    Application.Calculation = xlCalculationManual
    ActiveSheet.Copy After:=WB.Sheets(WB.Sheets.Count)
End Sub

(Don't forget to turn calculation back on after the test)

 

[EC1728]

What happened when you used F8 to single-step the code execution?

The following happens when using F8:

The first line highlighted - Set WB = ActiveSheet.Parent - Nothing happens that I can see - When I hover over the line it says: WB=Nothing

Next line highlighted - On Error GoTo CopyError - Nothing happens that I can see

Next line highlighted - For I = 1 To 10 - Nothing happens

Next line highlighted - S = S & ActiveSheet.Name & " --> " - Nothing happens - When I hover over it says: S=""

Next line highlighted - ActiveSheet.Copy After:=WB.Sheets(WB.Sheets.Count) - Nothing happens - When I over it says: WB.Sheets.Count=7

When I press F8 after that it makes a copy of the ActiveSheet and puts it at the end of my tabs then jumps to line S = S & ActiveSheet.Name & vbCr

When I hover over line S = S & ActiveSheet.Name & vbCr it says: S="29A-1 -->" It also says vbCr=""

Next line highlighted - Next I - It says I=1

Next it jumps back up to line - S = S & ActiveSheet.Name & " --> " It says: S="29A-1 --> 29A-1(2)"

Next it jumps to - ActiveSheet.Copy After:=WB.Sheets(WB.Sheets.Count) It says: WB.Sheets.Count=8

When I press F8 after that it makes a copy of the ActiveSheet and puts it at the end of my tabs then jumps to line S = S & ActiveSheet.Name & vbCr

S = S & ActiveSheet.Name & vbCr It says S="29A-1 --> 29A-1(2) 29A-1(2) -->"

Next I

Jumps back up to S = S & ActiveSheet.Name & " --> "

Next goes to - ActiveSheet.Copy After:=WB.Sheets(WB.Sheets.Count)

Next generates blank sheet with Sheet(#) then jumps down to line S = S & vbCr & "Copy Error." & vbCr It says: S="29A-1 --> 29A-1(2) 29A-1(2) -->" vbCr=""

Next goes to - S = S & Err.Description & vbCr It Says: S="29A-1 --> 29A-1(2) 29A-1(2) --> Copy Error" vbCr=""

Next goes to - S = S & "Sheets in workbook = " & WB.Sheets.Count & vbCr It says: S="29A-1 --> 29A-1(2) 29A-1(2) --> Copy Error.... WB.Sheets.Count=9 vbCr=""

Next goes to - For Each WS In WB.Sheets It says: WS = Nothing

Next goes to - S = S & " " & WS.Name & vbCr It says: S="29A-1 --> 29A-1(2) 29A-1(2) --> Copy Error.... Ws.Name = "Detail Sheet" vbCr = ""

Next goes to - Next WS

Next goes bak up to - S = S & " " & WS.Name & vbCr It says: S="29A-1 --> 29A-1(2) 29A-1(2) --> Copy Error.... Ws.Name = "Start Here" vbCr = ""

It continues to do this through all the sheet names

After it cycles through all the sheets it jumps down to S = S & vbCr It says: vbCr = False

Next goes to S = S & "Name of ActiveSheet = " & ActiveSheet.Name & vbCr

Next goes to S = S & "ActiveSheet Type = " & TypeName(ActiveSheet) & vbCr

Next goes to MsgBox S, vbInformation, "Copy Error" It says: vbInformation=64

Next it generates the Copy Error Report pop-up