Slow code to copy a row x # of times
 

This portion of my macro is running pretty slow. All it does is copy row 1
(with its formatting & formulas) enough times so that there will be a row for
each person in the company. We're only talking less than 500 people. Any
ideas to speed this process up? I thought about starting out with 300 rows
pre-populated (instead of 1) so my For loop could start at 300...in this
economy I'd hate to assume they will always have a minimum of 300 employees,
but if that's what it takes to speed this up, I'll do it. Many thanks!!

' Disable some features to speed up processing
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

' Add row for each employee to be imported
pct = 0
ctr = 1
imax = i
For i = i To 1 Step -1
Rows(1).Copy
Rows(13).Insert Shift:=xlDown
Rows(13).RowHeight = 12.75
Range("A13").Select
ctr = ctr + 1
pct = ctr / imax * 100
Application.StatusBar = "Importing census..." & pct & "% Complete"
Next i
Application.StatusBar = False
--
Please remember to indicate when the post is answered so others can benefit
from it later.

Slow code to copy a row x # of times
 

Unless I missed something, this should do what you want pretty quick. Just
adjust the receiving range for as many rows as you need. I only made it 88
for demo purposes.

Sub copyToMultRws()
Rows(1).Copy Range("A13:A100")
End Sub


"KC Rippstein" wrote:

This portion of my macro is running pretty slow. All it does is copy row 1
(with its formatting & formulas) enough times so that there will be a row for
each person in the company. We're only talking less than 500 people. Any
ideas to speed this process up? I thought about starting out with 300 rows
pre-populated (instead of 1) so my For loop could start at 300...in this
economy I'd hate to assume they will always have a minimum of 300 employees,
but if that's what it takes to speed this up, I'll do it. Many thanks!!

' Disable some features to speed up processing
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

' Add row for each employee to be imported
pct = 0
ctr = 1
imax = i
For i = i To 1 Step -1
Rows(1).Copy
Rows(13).Insert Shift:=xlDown
Rows(13).RowHeight = 12.75
Range("A13").Select
ctr = ctr + 1
pct = ctr / imax * 100
Application.StatusBar = "Importing census..." & pct & "% Complete"
Next i
Application.StatusBar = False
--
Please remember to indicate when the post is answered so others can benefit
from it later.

Slow code to copy a row x # of times
 

OMG!!!!! You hit the jackpot. This took a fraction of a second whereas
before it took several minutes. Thank you, thank you!!
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"JE McGimpsey" wrote:

One way:

Rows(1).Copy
With Rows(12)
.Offset(1).Resize(i).Insert Shift:=xlDown
.Offset(1).Resize(i).RowHeight = 12.75
End With


In article ,
KC Rippstein hotmail com <kcrippstein<atdot wrote:

This portion of my macro is running pretty slow. All it does is copy row 1
(with its formatting & formulas) enough times so that there will be a row for
each person in the company. We're only talking less than 500 people. Any
ideas to speed this process up? I thought about starting out with 300 rows
pre-populated (instead of 1) so my For loop could start at 300...in this
economy I'd hate to assume they will always have a minimum of 300 employees,
but if that's what it takes to speed this up, I'll do it. Many thanks!!

' Disable some features to speed up processing
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

' Add row for each employee to be imported
pct = 0
ctr = 1
imax = i
For i = i To 1 Step -1
Rows(1).Copy
Rows(13).Insert Shift:=xlDown
Rows(13).RowHeight = 12.75
Range("A13").Select
ctr = ctr + 1
pct = ctr / imax * 100
Application.StatusBar = "Importing census..." & pct & "% Complete"
Next i
Application.StatusBar = False