I have a workbook which creates and inserts additional sheets, based o
a list of items located elsewhere. The number is items in the lis
varies, but I would like the workbook to handle as many as possible.
Then, the macro pastes identical info onto each of the newly create
sheets. On each sheet then, there is about 750 rows and 20 columns o
data. The macro is designed to create, then paste, then create...

The problem I have, is that after around 75 sheets, the workbook slam
to a halt during the sheet creation process. I have found it seem
faster to select and paste entire columns, versus ranges of data
although the ranges are smaller.

Can anyone suggest any methods or changes I could make to speed thi
process up?

It also seems as though Excel doesn't always reset the worksheet coun
if I delete the sheets and start over. For instance, if it creates 9
sheets and I delete them, the next time it starts creating them, it ma
beging numbering them at 96.

Thanks for the help, sorry for the novel

--
Paul98
-----------------------------------------------------------------------
Paul987's Profile: http://www.excelforum.com/member.php...fo&userid=2485
View this thread: http://www.excelforum.com/showthread.php?threadid=38935

Would it help if I split the code into several marcos that ran one after
the other, or am I just running out of memory and there is nothing to do
about it.?


--
Paul987
------------------------------------------------------------------------
Paul987's Profile: http://www.excelforum.com/member.php...o&userid=24850
View this thread: http://www.excelforum.com/showthread...hreadid=389350

Paul987 wrote:
I have a workbook which creates and inserts additional sheets, based on
a list of items located elsewhere. The number is items in the list
varies, but I would like the workbook to handle as many as possible.
Then, the macro pastes identical info onto each of the newly created
sheets. On each sheet then, there is about 750 rows and 20 columns of
data. The macro is designed to create, then paste, then create...

The problem I have, is that after around 75 sheets, the workbook slams
to a halt during the sheet creation process. I have found it seems
faster to select and paste entire columns, versus ranges of data,
although the ranges are smaller.

Can anyone suggest any methods or changes I could make to speed this
process up?

It also seems as though Excel doesn't always reset the worksheet count
if I delete the sheets and start over. For instance, if it creates 95
sheets and I delete them, the next time it starts creating them, it may
beging numbering them at 96.

Thanks for the help, sorry for the novel.


--
Paul987
------------------------------------------------------------------------
Paul987's Profile: http://www.excelforum.com/member.php...o&userid=24850
View this thread: http://www.excelforum.com/showthread...hreadid=389350

Hi

If the data is the same in each sheet maybe you can try getting the
first sheet right
then copying it repeatedly.

Another possibility is to create a an array to contain the data and
assign the array
to appropriate ranges all at once

something like

Dim A As Variant
Dim R As Range
Dim ws As Worksheet
Dim i As Integer

ReDim A(1 to 250, 1 to 20)

'code to load A goes here

For i = 1 To NumSheets
Set ws = Workheets.Add
Set R = ws.Range("A1:T250")
R.Value = A
next i

you can also try doing things like turning off screen-updating during
the macro run if you haven't done so already.

I don't know about how to reset the count. I've noticed similar things
when I create and delete a lot of shapes. If the name that appears on
the tab is what you want to change then you can do it yourself via
something like ws.Name = "Sheet " & MyCount

Hope this helps

-John Coleman

A little on the simple side -- how often to you SAVE during this process?

That could make a big difference.

"scattered" wrote:



Paul987 wrote:
I have a workbook which creates and inserts additional sheets, based on
a list of items located elsewhere. The number is items in the list
varies, but I would like the workbook to handle as many as possible.
Then, the macro pastes identical info onto each of the newly created
sheets. On each sheet then, there is about 750 rows and 20 columns of
data. The macro is designed to create, then paste, then create...

The problem I have, is that after around 75 sheets, the workbook slams
to a halt during the sheet creation process. I have found it seems
faster to select and paste entire columns, versus ranges of data,
although the ranges are smaller.

Can anyone suggest any methods or changes I could make to speed this
process up?

It also seems as though Excel doesn't always reset the worksheet count
if I delete the sheets and start over. For instance, if it creates 95
sheets and I delete them, the next time it starts creating them, it may
beging numbering them at 96.

Thanks for the help, sorry for the novel.


--
Paul987
------------------------------------------------------------------------
Paul987's Profile: http://www.excelforum.com/member.php...o&userid=24850
View this thread: http://www.excelforum.com/showthread...hreadid=389350

Hi

If the data is the same in each sheet maybe you can try getting the
first sheet right
then copying it repeatedly.

Another possibility is to create a an array to contain the data and
assign the array
to appropriate ranges all at once

something like

Dim A As Variant
Dim R As Range
Dim ws As Worksheet
Dim i As Integer

ReDim A(1 to 250, 1 to 20)

'code to load A goes here

For i = 1 To NumSheets
Set ws = Workheets.Add
Set R = ws.Range("A1:T250")
R.Value = A
next i

you can also try doing things like turning off screen-updating during
the macro run if you haven't done so already.

I don't know about how to reset the count. I've noticed similar things
when I create and delete a lot of shapes. If the name that appears on
the tab is what you want to change then you can do it yourself via
something like ws.Name = "Sheet " & MyCount

Hope this helps

-John Coleman