Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Problem w/ workbook size, processing
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Controlling Workbook File Size? | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
How to hyperlink from a workbook to sheets in another workbook? | Excel Worksheet Functions | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) | |||
export chart - size problem | Charts and Charting in Excel |