Thread
:
macro speeds
View Single Post
#
11
Posted to microsoft.public.excel.programming
Under Pressure
external usenet poster
Posts: 22
macro speeds
Don
I' ve done some more work on this problem.
It's nothing to do with the macro.
I've tried simple copy and paste - both copying the 42 rows and just the
range a1:m42 - makes no difference - very slow indeed.
In fact, once it copies about 60 times, it runs out of memory.
I managed to save it after a copy of 30 and the file size was 24Gb.
Any idea what's going on?
Thanks again
Under Pressure
"Don Guillett" wrote:
Please do not type it in. Copy from here and paste. Then fire from the
active sheet.
If all else fails, send your workbook to my address below. But, try first.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Under Pressure" wrote in message
...
Thanks Don
I'd like to try this but not sure how to.
Do I type it as a macro?
What is the difference between the working of your code and my original?
Cheers.
Under Pressure
"Don Guillett" wrote:
try this in a REGULAR module. Notice the dots required for the WITH
statement
Sub copyit1()
ActiveSheet.ResetAllPageBreaks
With Sheets("sheet1")
For i = 1 To 3
.Rows("1:42").Copy .Cells(i * 42, "a")
ActiveWindow.SelectedSheets.HPageBreaks. _
Add Befo=.Cells(i * 42, "a")
Next i
End With
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Under Pressure" wrote in
message
...
Thanks, Niek
here it is !!!!!!
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Range("a1").Select
Rows("1:42").Select
Range("A42").Activate
Selection.Copy
i = o
Do Until i = 3
i = i + 1
Range("a1").Select
ActiveCell.Offset(42 * i, 0).Select
ActiveSheet.Paste
Range("A1").Select
ActiveCell.Offset(42 * i, 0).Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell
Loop
"Niek Otten" wrote:
Post the code of your macro
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
"Under Pressure" wrote in
message
...
| Under Pressure back online again looking for support.
|
| I have a small spreadsheet, 42 rows and 14 columns,with about 20
rows
(195
| cells in total) of conditional formats, 6 graphics,lots of text,
numbers and
| formulae.
|
| This forms a template that I need to copy 300 times so that data
from a
| second spreadsheet can populate it. I have written a macro to do
this.
|
| Something must be wrong because it takes far too long - to even copy
half a
| dozen or so. I can use my mouse to copy and paste it faster.
|
| I've done the usual things like turn the autocalculate off and
screen
update
| off.
|
| My macro copies the first 42 lines and then uses a simple loop to
paste
it
| in the appropriate place under the original 42 lines.
|
| Any suggestions?
Reply With Quote
Under Pressure
View Public Profile
Find all posts by Under Pressure