Help surpassing Error 1004


Board Regular
Jan 18, 2005
Thats me.

I'm creating 100+ worksheets in a workbook by copying an existing sheet in that same workbook. (I've also tried copying from an external template but get the same issue). Somewhere throughout the copying I get the Copy Method Worksheet Class 1004 error and the macro craps out.

The template work around didn't work so I'm attempting to open and close the workbook every 30 copies.

Here's the catch. I am naming the worksheets based on a column of values and can't figure out how to resume copying where I left off once closing and reopening the subject file.

Any suggestions??

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
You need to set the "Application.CutCopyMode = False" after the paste to clear the clip-board, you are most likely running out of memory space!
Upvote 0
Hey Thanks Joe. I figured I was running out of memory from what I'd read online.
I used the Application.cutcopymode = false and I get a Type Mismatch Error 13, that I'm not sure how to overcome.
Upvote 0
NOPE...I got the cutcopymode to work and the macro gets a little farther in its adding of sheets, but still ends up crapping out with the same error.

Another successful failure...but 1 step closer...
Upvote 0
This will copy sheet 3 to the end of the workbook and name it: Sheet3(2)

The copy paste method is real sensitive, every time I work with it I am forced to play with it. Even if I copy it from a workbook that works now, the data or situation in the new workbook may give me that: Type Mismatch Error 13.

Most of the time it is fixed by correcting the way the data is selected, that is if a Set is used or a Var.

This may help?

Sub myNewData()
'Standard Module code, Like: Module1.
'Ask for new sheet name and copy all data from MyData to the new sheet.
Dim Message$, Title$, Default$, MySheet$, MyList$, MyTest
Dim Message2$, Title2$, Default2$, MyData$
MyTest = False
MyData = "Sheet1"

'Message, title, and default value.
Message = "Enter a New ""Sheet Name"" to add to this workBook:" ' Set prompt.
Title = "Get Sheet Name!" ' Set title.
Default = "TestSheet" ' Set default.

'Get New Sheet Name.
MySheet = InputBox(Message, Title, Default)

'This adds a sheet and names it "your name" or go's to the sheet inputed if it exists.
'Get all sheets name and test for new sheet name.
For Each ws In Worksheets
If ws.Name = MySheet Then
MyTest = True
End If
Next ws
If MyTest <> True Then
Sheets.Add.Name = MySheet
End If

'This selects your new sheet and moves it after sheet "MyData," which could be any sheet name.
Sheets(MySheet).Move After:=Sheets(MyData)

'This selects the sheet with the data and its range.
'Message, title, and default value.
Message2 = "Enter the Sheet name to get your data from:" ' Set prompt.
Title2 = "Get Data Sheet Name!" ' Set title.
Default2 = "Sheet1" ' Set default.

'Get Data Sheet Name.
MyData = InputBox(Message2, Title2, Default2)
Sheets(MyData).Range(Range("A1"), Sheets(MyData).Range("A65536").End(xlUp)).Select

'This will copy and paste the data to your new sheet.

'Test for existing Data on copy to sheet.
If Sheets(MySheet).Range("A1").Value <> "" Then
Sheets(MySheet).Range("A65536").End(xlUp).Offset(1, 0).Select
End If
'Paste data from MyData to your new sheet.

'At this point your data will be on the new sheet and selected for the next step.
End Sub
Upvote 0

Forum statistics

Latest member

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
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 "".
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