Need Help - How do I stop using select

[General Specific]

The following code checks for data in the first cell in the row and
then copies and pastes the formulas. This pastes the correct formulas
to a row if data is present.

The problem is, Application.screenupdate=false does not work since
each select command turns it back on.

So, this code is slow. how do I fix this?


i = 16
While Not IsEmpty(Cells(i, 1))

Range ("I15")
Selection.Copy
Cells(i, 9).Select
ActiveSheet.Paste
Range("J15").Select
Selection.Copy
Cells(i, 10).Select
ActiveSheet.Paste
Range("K15").Select
Selection.Copy
Cells(i, 11).Select
ActiveSheet.Paste
i = i + 1
Wend

 

[Per Jessen]

Hi

No need to select cells, it is slowing down your code.

Look at this:

i = 16
While Not IsEmpty(Cells(i, 1))
Range("I15").Copy
Cells(i, 9).Select
ActiveSheet.Paste Destination:=Cells(i, 9)
Range("J15").Copy
ActiveSheet.Paste Destination:=Cells(i, 10)
Range("K15").Copy
ActiveSheet.Paste Destination:=Cells(i, 11)
i = i + 1
Wend

Best regards,
Per

 

[Bob Umlas, Excel MVP]

First, it's Application.Screenupdating = False

Next:
Application.Calculation = xlCalculationManual
i = 16
While Not IsEmpty(Cells(i, 1))
Range ("I15:K15").Copy Cells(i,9)
i = i + 1
Wend


But even faster (no looping):
Sub DoItAll()
'Untested
Lastrow=Range("A16").end(xlDown).Row
Range("I15:K15").Copy Range("I16").resize(LastRow-15,3)
End Sub

HTH
Bob Umlas
Excel MVP

 

[General Specific]

Wow!

Thanks guys!