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

EC1728

New Member
Joined
May 12, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
863
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
FWIW, I named a worksheet 29-1 and ran the code you posted:

VBA Code:
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

New Member
Joined
May 12, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
FWIW, I named a worksheet 29-1 and ran the code you posted:

VBA Code:
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

Well-known Member
Joined
Apr 5, 2021
Messages
927
Office Version
  1. 2010
Platform
  1. Windows
Hi,​
try if it works on a brand new workbook …​
 

EC1728

New Member
Joined
May 12, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Apr 5, 2021
Messages
927
Office Version
  1. 2010
Platform
  1. Windows
Could you test on an older Excel version like 2013 or 2010 ?​
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
863
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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.

VBA Code:
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

Active Member
Joined
Nov 7, 2011
Messages
439
I whipped up the following code and it works for me ...

VBA Code:
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

New Member
Joined
May 12, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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?
 

Attachments

  • Copy Error.PNG
    Copy Error.PNG
    14.8 KB · Views: 5
  • Copy Error2.PNG
    Copy Error2.PNG
    11.1 KB · Views: 5

Watch MrExcel Video

Forum statistics

Threads
1,132,890
Messages
5,655,820
Members
418,239
Latest member
RSadler11

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