Speed the macro works
 

I am relatively new to macro writing. I have written a macro that copies 30
lines of a spreadsheet ( this is a template for student report ) 300 times
into the rows below it. Works fine at first, quite fast as one wouls expect.
However, as more sections of the 30 lines are copied, it goes slower and
slower and takes the best part of 20mins to complete. Any suggestions?

Under Pressure

Speed the macro works
 

Are there lots of formulas in those 30 lines?
If so, try this before your code:
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = FALSE
and this after:

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Darren

Under Pressure wrote:
I am relatively new to macro writing. I have written a macro that copies 30
lines of a spreadsheet ( this is a template for student report ) 300 times
into the rows below it. Works fine at first, quite fast as one wouls expect.
However, as more sections of the 30 lines are copied, it goes slower and
slower and takes the best part of 20mins to complete. Any suggestions?

Under Pressure

Speed the macro works
 

The first thing is to make sure calculation mode is manual, at least while
the macro is running.

Also it may not be necessary for the macro to do more than one copy/paste,
depending on the layout of your data. A simplified example:

Range("A1:B3").AutoFill Range("A1:B99"), xlFillCopy

Here, there is data in the first two rows of the source range, A1:B2, and
row 3 is blank. I want to copy down the pattern of 2 data rows and 1 blank
row multiple times. Rather than doing a loop the above does it all at once.

--
Jim
"Under Pressure" wrote in message
...
I am relatively new to macro writing. I have written a macro that copies
30
lines of a spreadsheet ( this is a template for student report ) 300 times
into the rows below it. Works fine at first, quite fast as one wouls
expect.
However, as more sections of the 30 lines are copied, it goes slower and
slower and takes the best part of 20mins to complete. Any suggestions?

Under Pressure

Speed the macro works
 

Thanks Darren

No calculations - just lots of coditional formatting.

I'll try your idea.

Cheers

Under Pressure

"Darren Hill" wrote:

Are there lots of formulas in those 30 lines?
If so, try this before your code:
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = FALSE
and this after:

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Darren

Under Pressure wrote:
I am relatively new to macro writing. I have written a macro that copies 30
lines of a spreadsheet ( this is a template for student report ) 300 times
into the rows below it. Works fine at first, quite fast as one wouls expect.
However, as more sections of the 30 lines are copied, it goes slower and
slower and takes the best part of 20mins to complete. Any suggestions?

Under Pressure

Speed the macro works
 

Jim

Told you I was new to this macro writing. This is a good idea that I will
try. You want to see the macro that I've written to copy each row of a 300
line spreadsheet into this template!!!

Any suggestions as to where I might get a set of useful commands - I've been
learning his macro stuff by using key strokes and then looking at the
generated code.

Thanks again

Under Pressure

"Jim Rech" wrote:

The first thing is to make sure calculation mode is manual, at least while
the macro is running.

Also it may not be necessary for the macro to do more than one copy/paste,
depending on the layout of your data. A simplified example:

Range("A1:B3").AutoFill Range("A1:B99"), xlFillCopy

Here, there is data in the first two rows of the source range, A1:B2, and
row 3 is blank. I want to copy down the pattern of 2 data rows and 1 blank
row multiple times. Rather than doing a loop the above does it all at once.

--
Jim
"Under Pressure" wrote in message
...
I am relatively new to macro writing. I have written a macro that copies
30
lines of a spreadsheet ( this is a template for student report ) 300 times
into the rows below it. Works fine at first, quite fast as one wouls
expect.
However, as more sections of the 30 lines are copied, it goes slower and
slower and takes the best part of 20mins to complete. Any suggestions?

Under Pressure

Speed the macro works
 

Are you using something similar to this:

Sheets("Sheet1").Select
Range("A1").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste


If so, this type of code may have a lot of overhead that ties up too much
memory. Instead give something like this a shot:

Sheets("Sheet2").Range("A1").Value = Sheets("Sheet1").Range("A1").Value


Just a thought...

Mark Ivey


"Under Pressure" wrote in message
...
I am relatively new to macro writing. I have written a macro that copies
30
lines of a spreadsheet ( this is a template for student report ) 300 times
into the rows below it. Works fine at first, quite fast as one wouls
expect.
However, as more sections of the 30 lines are copied, it goes slower and
slower and takes the best part of 20mins to complete. Any suggestions?

Under Pressure

Speed the macro works
 

Jim

I've now tried your idea. Works OK but doesn't copy the formats of each
row. Some of the 31 rows that are being copied have different heights
because some of the cells contain a logo, text boxes while others contain
numbers that have conditional formats in them.

I've tried adapting the Range("a1:b3") at the beginning to Rows("1:3") but
this doesn't work.

As for turning the autocalculate off, a marginal increase in speed was
detected.

Thanks again

Under Pressure

"Jim Rech" wrote:

The first thing is to make sure calculation mode is manual, at least while
the macro is running.

Also it may not be necessary for the macro to do more than one copy/paste,
depending on the layout of your data. A simplified example:

Range("A1:B3").AutoFill Range("A1:B99"), xlFillCopy

Here, there is data in the first two rows of the source range, A1:B2, and
row 3 is blank. I want to copy down the pattern of 2 data rows and 1 blank
row multiple times. Rather than doing a loop the above does it all at once.

--
Jim
"Under Pressure" wrote in message
...
I am relatively new to macro writing. I have written a macro that copies
30
lines of a spreadsheet ( this is a template for student report ) 300 times
into the rows below it. Works fine at first, quite fast as one wouls
expect.
However, as more sections of the 30 lines are copied, it goes slower and
slower and takes the best part of 20mins to complete. Any suggestions?

Under Pressure

Speed the macro works
 

People might be able to help better if you post your code. But my
suggestion would be to break it into two steps:

1. use the autofill method suggested by Jim

2. then copy the formats and conditionalformats from the original range
to the whole range.

Darren
Under Pressure wrote:
Jim

I've now tried your idea. Works OK but doesn't copy the formats of each
row. Some of the 31 rows that are being copied have different heights
because some of the cells contain a logo, text boxes while others contain
numbers that have conditional formats in them.

I've tried adapting the Range("a1:b3") at the beginning to Rows("1:3") but
this doesn't work.

As for turning the autocalculate off, a marginal increase in speed was
detected.

Thanks again

Under Pressure

"Jim Rech" wrote:

The first thing is to make sure calculation mode is manual, at least while
the macro is running.

Also it may not be necessary for the macro to do more than one copy/paste,
depending on the layout of your data. A simplified example:

Range("A1:B3").AutoFill Range("A1:B99"), xlFillCopy

Here, there is data in the first two rows of the source range, A1:B2, and
row 3 is blank. I want to copy down the pattern of 2 data rows and 1 blank
row multiple times. Rather than doing a loop the above does it all at once.

--
Jim
"Under Pressure" wrote in message
...
I am relatively new to macro writing. I have written a macro that copies
30
lines of a spreadsheet ( this is a template for student report ) 300 times
into the rows below it. Works fine at first, quite fast as one wouls
expect.
However, as more sections of the 30 lines are copied, it goes slower and
slower and takes the best part of 20mins to complete. Any suggestions?

Under Pressure

Speed the macro works
 

Well I find that filling copies everything a normal copy does. As for row
heights this works for me:

Range("1:3").AutoFill Range("1:99"), xlFillCopy

--
Jim
"Under Pressure" wrote in message
...
Jim

I've now tried your idea. Works OK but doesn't copy the formats of each
row. Some of the 31 rows that are being copied have different heights
because some of the cells contain a logo, text boxes while others contain
numbers that have conditional formats in them.

I've tried adapting the Range("a1:b3") at the beginning to Rows("1:3") but
this doesn't work.

As for turning the autocalculate off, a marginal increase in speed was
detected.

Thanks again

Under Pressure

"Jim Rech" wrote:

The first thing is to make sure calculation mode is manual, at least
while
the macro is running.

Also it may not be necessary for the macro to do more than one
copy/paste,
depending on the layout of your data. A simplified example:

Range("A1:B3").AutoFill Range("A1:B99"), xlFillCopy

Here, there is data in the first two rows of the source range, A1:B2, and
row 3 is blank. I want to copy down the pattern of 2 data rows and 1
blank
row multiple times. Rather than doing a loop the above does it all at
once.

--
Jim
"Under Pressure" wrote in
message
...
I am relatively new to macro writing. I have written a macro that
copies
30
lines of a spreadsheet ( this is a template for student report ) 300
times
into the rows below it. Works fine at first, quite fast as one wouls
expect.
However, as more sections of the 30 lines are copied, it goes slower
and
slower and takes the best part of 20mins to complete. Any suggestions?

Under Pressure

Speed the macro works
 

Hi Darren,
I tried this for my code which used to take 30+ minutes to execute. Guess
what, it takes less than 2 minutes now!!!
Thank you so much.

Regards
Rajesh

"Darren Hill" wrote:

Are there lots of formulas in those 30 lines?
If so, try this before your code:
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = FALSE
and this after:

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Darren

Under Pressure wrote:
I am relatively new to macro writing. I have written a macro that copies 30
lines of a spreadsheet ( this is a template for student report ) 300 times
into the rows below it. Works fine at first, quite fast as one wouls expect.
However, as more sections of the 30 lines are copied, it goes slower and
slower and takes the best part of 20mins to complete. Any suggestions?

Under Pressure