Speed the macro works

[Under Pressure]

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

 

[Darren Hill]

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

 

[Jim Rech]

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.

 

[Under Pressure]

Thanks Darren

No calculations - just lots of coditional formatting.

I'll try your idea.

Cheers

Under Pressure

 

[Under Pressure]

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

 

[Mark Ivey]

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]

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

 

[Darren Hill]

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

 

[Jim Rech]

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

 

[rajesh_taurien]

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