Help surpassing Error 1004

ectoid

Board Regular
Joined
Jan 18, 2005
Messages
97
:oops:
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??
Thanks,
-Eric
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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!
 

ectoid

Board Regular
Joined
Jan 18, 2005
Messages
97
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.
 

ectoid

Board Regular
Joined
Jan 18, 2005
Messages
97
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...
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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).Select
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).Select
Sheets(MyData).Range("A1").Select
Sheets(MyData).Range(Range("A1"), Sheets(MyData).Range("A65536").End(xlUp)).Select

'This will copy and paste the data to your new sheet.
Selection.Copy
Sheets(MyData).Select
Sheets(MyData).Range("A1").Select

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

'At this point your data will be on the new sheet and selected for the next step.
Sheets(MySheet).Select
Sheets(MySheet).Range("A1").Select
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,447
Messages
5,642,209
Members
417,262
Latest member
andrewd1

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