Copy Method of Workseet Class Failed

  • Thread starter Thread starter Tom Kosensky
  • Start date Start date
T

Tom Kosensky

Hey there,

I am having an issue with one of my macros, and was wondering if anyone here
can help.

To start off, I have searched throughout Google, and it seems that the only
answer I can find on there has to do with issues in previous versions of
Excel - problems that were solved in Excel 2002, which is what I have.

Anyways, I have a fairly big project where I am running 400 records of data
through a macro - it runs fine up until record 280, when I get the error
listed above. Pretty much, the 400 records are getting split into MANY
different workbooks - up to 100 different workbooks based on various
criteria.

Here is the code itself:

Set wkb = Workbooks("Initialize.xls")
wkb.Activate
Sheets("Template").Select

''The next line is where the error occurs
Sheets("Template").Copy Before:=Workbooks(WksVar.Range("Filename").Value &
"msts.xls").Worksheets(1)

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
 
Is it possible, that you have too many worksheets in the
workbook, where you are trying to copy a new sheet. The
only limitation of sheets in a workbook in Excel 2002 is
the amount of available memory.

Ecco
-----Original Message-----
Hey there,

I am having an issue with one of my macros, and was wondering if anyone here
can help.

To start off, I have searched throughout Google, and it seems that the only
answer I can find on there has to do with issues in previous versions of
Excel - problems that were solved in Excel 2002, which is what I have.

Anyways, I have a fairly big project where I am running 400 records of data
through a macro - it runs fine up until record 280, when I get the error
listed above. Pretty much, the 400 records are getting split into MANY
different workbooks - up to 100 different workbooks based on various
criteria.

Here is the code itself:

Set wkb = Workbooks("Initialize.xls")
wkb.Activate
Sheets("Template").Select

''The next line is where the error occurs
Sheets("Template").Copy Before:=Workbooks(WksVar.Range ("Filename").Value &
"msts.xls").Worksheets(1)

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues,
Click to expand...
Operation:=xlNone, SkipBlanks:= _
 
Back
Top