Problem w/ workbook size, processing

[Paul987]

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.

[FSt1]

hi,
i think your real problem is the copy/paste part. It has been my experience
that one should avoid using the copy and paste command in a macro multiple
times. one or two time is ok but massive use of copy and paste will
eventually crash the macro usually with all kinds of memory problems/error
messages.
here is some sample code i wrote as a demo of how to use varialbles to make
one range of data equal another range of blank cells. it works just like
copy/paste but doesn't use the clipboard(which i suspect it the real problem).
I tested this and move 20 columns and 1056 rows of data from sheet 1 to
sheet 2.
you may have to adjust it to fit your data and place it in your loop
somewhere. if it doesn't help maybe it will give you ideas. remember. avoid
using the clipboard massive numbers of times in a macro.

Sub macCopyRange()
Dim rng As Range 'range to copy
Dim rng1 As Range 'copy to range
Dim rcnt As Long 'row counter
Dim ccnt As Long 'column counter
Sheet1.select
Set rng = Range(Range("A1"), Range("A1").End(xlDown).Offset(0, 20))
'Selects the range to copy
rcnt = rng.Rows.Count - 1 'sizes the range - counts rows
ccnt = rng.Columns.Count - 1 ' sizes the range - counts columns
'note-Ranges must be the same
size. vital
Sheet2.select
Set rng1 = Range(Range("AA1"), Range("AA1").Offset(rcnt, ccnt))
'sets the "Copy to" range

rng1.Value = rng.Value 'moves a copy of the data from one range to the
other.

End Sub

Regards

FSt1

"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=389427
>
>

[Kassie]

Hi

Cannot for the life of me understand why you would want to paste identical
data into so many sheets! 750 Rows and 20 Columns of identical data, and
that on 95 sheets. why?
--
[email protected]ve_2nd_at. Randburg, Gauteng, South Africa


"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=389427
>
>