I have a model that is used to download companies' financial data from
a database. For purposes of formatting for printing, I copy the whole
of a template worksheet using the VBA code Comp0.Copy
After:=Sheets(Worksheets.Count). Comp0 is the codename for the
template sheet. Onto the newly created sheet, I then copy preformatted
data for a company. Everything works fine until I make about 30 of
these worksheet copies, and then the macro jams. If I then save the
file, and open it up again, the sequence works again--I assume because
some memory block gets cleared up when you close and restart the file.

Has anybody else had this problem, and is there a way to avoid it?
(One workaround is to insert a new worksheet, copy everything over,
redo the page setup settings for the printing. But specific print
ranges in the sheet present other problems.)

Give this a look...

http://support.microsoft.com/default...84&Product=xlw
--
HTH...

Jim Thomlinson


"jayray" wrote:

I have a model that is used to download companies' financial data from
a database. For purposes of formatting for printing, I copy the whole
of a template worksheet using the VBA code Comp0.Copy
After:=Sheets(Worksheets.Count). Comp0 is the codename for the
template sheet. Onto the newly created sheet, I then copy preformatted
data for a company. Everything works fine until I make about 30 of
these worksheet copies, and then the macro jams. If I then save the
file, and open it up again, the sequence works again--I assume because
some memory block gets cleared up when you close and restart the file.

Has anybody else had this problem, and is there a way to avoid it?
(One workaround is to insert a new worksheet, copy everything over,
redo the page setup settings for the printing. But specific print
ranges in the sheet present other problems.)

Maybe...
http://support.microsoft.com/default...b;en-us;210684
"Copying Worksheet Programmatically Causes Run-Time Error 1004 in Excel"
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"jayray"
wrote in message
I have a model that is used to download companies' financial data from
a database. For purposes of formatting for printing, I copy the whole
of a template worksheet using the VBA code Comp0.Copy
After:=Sheets(Worksheets.Count). Comp0 is the codename for the
template sheet. Onto the newly created sheet, I then copy preformatted
data for a company. Everything works fine until I make about 30 of
these worksheet copies, and then the macro jams. If I then save the
file, and open it up again, the sequence works again--I assume because
some memory block gets cleared up when you close and restart the file.

Has anybody else had this problem, and is there a way to avoid it?
(One workaround is to insert a new worksheet, copy everything over,
redo the page setup settings for the printing. But specific print
ranges in the sheet present other problems.)

On Aug 3, 6:44 pm, "Jim Cone" wrote:
Maybe...http://support.microsoft.com/default...b;en-us;210684
"Copying Worksheet Programmatically Causes Run-Time Error 1004 in Excel"
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"jayray"
wrote in message
I have a model that is used to download companies' financial data from
a database. For purposes of formatting for printing, I copy the whole
of a template worksheet using the VBA code Comp0.Copy
After:=Sheets(Worksheets.Count). Comp0 is the codename for the
template sheet. Onto the newly created sheet, I then copy preformatted
data for a company. Everything works fine until I make about 30 of
these worksheet copies, and then the macro jams. If I then save the
file, and open it up again, the sequence works again--I assume because
some memory block gets cleared up when you close and restart the file.

Has anybody else had this problem, and is there a way to avoid it?
(One workaround is to insert a new worksheet, copy everything over,
redo the page setup settings for the printing. But specific print
ranges in the sheet present other problems.)

Thanks! Very helpful to know this is a bug.