macro speeds
 

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?

macro speeds
 

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?

macro speeds
 

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?

macro speeds
 

Niek
Already posted the code.

Want to look at the spreadsheet?

If so, how do I post it?

Thanks agaon

Under Pressure


"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?

macro speeds
 

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?

macro speeds
 

Your problem is the "i=o" - you've got the letter o and not the number 0. It
could be treating i as a string, and appending 1 to the string, so after a
few loops it will be o111111111111111111, and never 3.

"Under Pressure" wrote:

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?

macro speeds
 

Sam

Rather stupid of me, eh?

However, it didn't help. Presumably it assumed that letter'o' had the value
0 as the macro is run. Hence setting variable I to zero.

Macro is still incredibly slow - can't think why. It's only copying 42
lines. Even when I step through the macro one line at a time it pauses for
ages on the paste line.

Any more suggestions?

Under Pressure


"Sam Wilson" wrote:

Your problem is the "i=o" - you've got the letter o and not the number 0. It
could be treating i as a string, and appending 1 to the string, so after a
few loops it will be o111111111111111111, and never 3.

"Under Pressure" wrote:

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?

macro speeds
 

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?

macro speeds
 

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?

macro speeds
 

Thanks for the speedy response - much appreciated.

Sorry to say that it didn't work - after a while got the message 'not
respnding' and had to exit.

Thanks for the offer of having a look at it, very kind of you.

Best wishes

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?

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?

macro speeds
 

That form takes a ton of memory. The macro that I gave you would do it as
desired but for that. I've been trying to tell you that it will NOT work
that way. You must do as I suggested by having ONE form that you fill and
copy to a database sheet and another macro to bring the data(s) back for
work and printing.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Under Pressure" wrote in message
...
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?

macro speeds
 

Don, thanks

I think that one of your replies to me has gone missing. I don't remeber
you suggesting "You must do as I suggested by having ONE form that you fill
and copy to a database sheet and another macro to bring the data(s) back for
work and printing." I'll have a go at this and if all else fails I can the
work in batches of 30 - just means that I have to hang around while its
doing it.

Thanks again

Under Pressure

"Don Guillett" wrote:


That form takes a ton of memory. The macro that I gave you would do it as
desired but for that. I've been trying to tell you that it will NOT work
that way. You must do as I suggested by having ONE form that you fill and
copy to a database sheet and another macro to bring the data(s) back for
work and printing.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Under Pressure" wrote in message
...
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?

macro speeds
 

Look at the private emails I sent to you. If you didn't get them, send me a
private email and I will respond. What you are trying to do will not work
and, even if it would, is a BAD idea.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Under Pressure" wrote in message
...
Don, thanks

I think that one of your replies to me has gone missing. I don't remeber
you suggesting "You must do as I suggested by having ONE form that you
fill
and copy to a database sheet and another macro to bring the data(s) back
for
work and printing." I'll have a go at this and if all else fails I can
the
work in batches of 30 - just means that I have to hang around while its
doing it.

Thanks again

Under Pressure

"Don Guillett" wrote:


That form takes a ton of memory. The macro that I gave you would do it as
desired but for that. I've been trying to tell you that it will NOT work
that way. You must do as I suggested by having ONE form that you fill and
copy to a database sheet and another macro to bring the data(s) back for
work and printing.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Under Pressure" wrote in
message
...
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?