copy method of excel failed'

I am running a vba code to add about 200 sheets...my code generates a sheet in one file and then pastes it in another file....after generating nad adding 38 sheets i get the error copy method of excel failed

i tried clearing the clipboard and resuming the code but that didn,t work

any clue as to what is the problem?

Thanks!
Ashish

here it is...

Sub depotfrance()

Dim isin As String
Dim company As String
Dim j As Integer

For j = 1 To 200

Workbooks("france print soft.xls").Activate
Worksheets("test1").Range("A1:u888").ClearContents

isin = Worksheets("Raw data matched").Range("i422").Offset(j - 1, 0).Value
company = Worksheets("Raw data matched").Range("f422").Offset(j - 1, 0).Value
Worksheets("template").Range("b5").Value = isin
Worksheets("template").Range("b3").Value = company
Worksheets("template").Calculate
numsheets = Workbooks("France2.xls").Sheets.Count
Sheets("template").Copy After:=Workbooks("France2.xls").Sheets(numsheets)
Workbooks("France2.xls").Sheets("template").Name = isin



Workbooks("france print soft.xls").Activate
Worksheets("accd2").Select
    
Selection.AutoFilter Field:=2, Criteria1:=isin

Columns("A:U").Select
    Selection.Copy
    Sheets("test1").Select
    
    Range("A2").Select
    ActiveSheet.Paste
    
numsheets = Workbooks("France2.xls").Sheets.Count
Sheets("test1").Copy After:=Workbooks("France2.xls").Sheets(numsheets)
Workbooks("France2.xls").Sheets("test1").Name = isin & "drill"

Next j

End Sub

can someone help me with a code for clearing my clipboard...that might solve my problem

france print soft has got all the data....there are a lot of companies and for each company I have to generate a 'template' sheet and a 'test1' sheet

I am copying the stock ISIN and the stock name from 'raw matched data' file and pasting on the template sheet...I then copy the template sheet to the final 'france2' file...then I rename this sheet using the company's isin

the france print soft file has an 'accd2' sheet which is autofiltered...i search the stock's isin here and copy the result in 'test1'...i then copy 'test1' into 'france2' and rename this sheet in france2 as isin&drill

i am running the loop for 200 stocks but the code stops after working for 18 stocks

the code breaks at

Sheets("test1").Copy After:=Workbooks("France2.xls").Sheets(numsheets)

Originally Posted by ashish727

any clue as to what is the problem?

See Microsoft support article 210684

Copying worksheet programmatically causes run-time error 1004 in Excel

The MS support article gives 2 workarounds:

1. Save and close the workbook periodically in the copy sheets loop
2. Insert a new worksheet from a template instead of copying an existing worksheet

thanks a lot guys!

my 'test1' sheet had too many rows actually...instead of copying the whole sheet I just copied the used range and that worked for me...