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.

 

[rlv01]

FWIW, I named a worksheet 29-1 and ran the code you posted:

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

It created as many copied sheets as I wanted. I stopped after 12

29A-1, 29A-1 (2), 29A-1 (3), 29A-1 (4), 29A-1 (5), 29A-1 (6), 29A-1 (7), 29A-1 (8), 29A-1 (9), 29A-1 (10), 29A-1 (11), 29A-1 (12).

 

[EC1728]

FWIW, I named a worksheet 29-1 and ran the code you posted:

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

It created as many copied sheets as I wanted. I stopped after 12

29A-1, 29A-1 (2), 29A-1 (3), 29A-1 (4), 29A-1 (5), 29A-1 (6), 29A-1 (7), 29A-1 (8), 29A-1 (9), 29A-1 (10), 29A-1 (11), 29A-1 (12).

rlv01,

Thank you for the response. I attempted opening a new file as well to check if there was a problem with the file I was working on. Unfortunately, I am still having the same issue when I try to open a new document. Each time I run the code I get the same error and it only produces a blank sheet. I also attempted using the move or copy option and still no luck.

I also attempted to open Excel in Safe Mode as another site suggested but I still had the same issues. Not sure what the problem is but it has completely stopped my project from moving forward.

 

[Marc L]

Hi,​

​

try if it works on a brand new workbook …​

 

[EC1728]

Hi,​

​

try if it works on a brand new workbook …​

Marc,

I opened a brand new workbook. After opening a brand new workbook I used the Move or Copy option and it worked with no issue. I also used the Ctrl and drag copy option and it worked with no issues. I opened VBA and inserted a new module. I inserted the code from my original post. It worked the first time and the second time I got the Run-time error '1004'

I noticed that after I get the Run-time error is when I start having the same issues with the Move or Copy option and the Ctrl and drag copy option. After the Run-time error, the Move and Copy option will only work one time then after that it will only produce a blank sheet.

So I end up with:

29A-1, 29A-1 (2), 29A-1 (3), 29A-1 (4), 29A-1 (5), 29A-1 (6), 29A-1 (7), Sheet8, 29A-1 (8), Sheet9, Sheet10, Sheet11

All the tabs listed as Sheet(#) are all blank.

If I open a new workbook and try to just run the VBA code first, I immediately get the Run-Time Error on the first attempt and then get the issues listed above.

It appears to have something to do with the VBA code causing the issue.

 

[Marc L]

​

Could you test on an older Excel version like 2013 or 2010 ?​

 

[rlv01]

Try this debug code which attempts to create 10 copies at one time. Use the debugger to single step through it and watch for any unexpected changes to ActiveSheet or WB.

Sub CopyActiveSheet()
    Dim WB As Workbook, WS As Worksheet
    Dim I As Integer, S As String
    Set WB = ActiveSheet.Parent
    
    On Error GoTo CopyError
    
    For I = 1 To 10
        S = S & ActiveSheet.Name & " --> "

        ActiveSheet.Copy After:=WB.Sheets(WB.Sheets.Count)
        
        S = S & ActiveSheet.Name & vbCr
          
    Next I
    MsgBox S, vbInformation, "Copied"
   Exit Sub
    
CopyError:
    S = S & vbCr & "Copy Error." & vbCr
    S = S & Err.Description & vbCr
    S = S & "Sheets in workbook = " & WB.Sheets.Count & vbCr
    For Each WS In WB.Sheets
        S = S & "    " & WS.Name & vbCr
    Next WS
    
    S = S & vbCr
    S = S & "Name of ActiveSheet = " & ActiveSheet.Name & vbCr
    S = S & "ActiveSheet Type = " & TypeName(ActiveSheet) & vbCr
    MsgBox S, vbInformation, "Copy Error"
End Sub

 

[johnnyL]

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.

 

[EC1728]

Try this debug code which attempts to create 10 copies at one time. Use the debugger to single step through it and watch for any unexpected changes to ActiveSheet or WB.

Sub CopyActiveSheet()
    Dim WB As Workbook, WS As Worksheet
    Dim I As Integer, S As String
    Set WB = ActiveSheet.Parent
   
    On Error GoTo CopyError
   
    For I = 1 To 10
        S = S & ActiveSheet.Name & " --> "

        ActiveSheet.Copy After:=WB.Sheets(WB.Sheets.Count)
       
        S = S & ActiveSheet.Name & vbCr
         
    Next I
    MsgBox S, vbInformation, "Copied"
   Exit Sub
   
CopyError:
    S = S & vbCr & "Copy Error." & vbCr
    S = S & Err.Description & vbCr
    S = S & "Sheets in workbook = " & WB.Sheets.Count & vbCr
    For Each WS In WB.Sheets
        S = S & "    " & WS.Name & vbCr
    Next WS
   
    S = S & vbCr
    S = S & "Name of ActiveSheet = " & ActiveSheet.Name & vbCr
    S = S & "ActiveSheet Type = " & TypeName(ActiveSheet) & vbCr
    MsgBox S, vbInformation, "Copy Error"
End Sub

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?

 

[Marc L]

Any thoughts?

Post #6 …​