formula pasting is very slow

[dreamer]

I have a worksheet with formula's in first row. Depending on the use
interaction the fomula's are copied accros the worksheet. for exampl
if user fills column A up to row 100, the formula's from the first ro
are copied up to row 100. Well this all works fine but is very slo
(about 15 seconds). I have timed it and it's just copying and pastin
that takes this long, not calculating. Also if I want to remove th
formula's afterwards it takes ages (I just select the range and hi
delete). The range usually goes from C3 to IV130. Any way to faste
things up

 

[Harald Staff]

Post the code please.

 

[dreamer]

Sub Proces()
Application.ScreenUpdating = False
Dim ProcesID As Object
Dim time1 As Date
Dim time2 As String

time1 = Timer
PBSP.Select 'worksheet
Set ProcesID = PBSP.Range("A3:A100")
Actions = Application.WorksheetFunction.CountA(ProcesID)
If Actions > 0 Then
Range("J1:IV1").Select
Selection.Copy
Range("J3:IV" & Actions + 2).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End If

'Calculate

Debug.Print tijd
Application.ScreenUpdating = True

End Su

 

[dreamer]

Well, the whole code is a bit long so here is just the sub that does th
copying and pasting.

Sub Proces()
Application.ScreenUpdating = False
Dim ProcesID As Object
Dim time1 As Date
Dim time2 As String

time1 = Timer
PBSP.Select 'worksheet
Set ProcesID = PBSP.Range("A3:A1000")
Actions = Application.WorksheetFunction.CountA(ProcesID)
If Actions > 0 Then
Range("J1:IV1").Select
Selection.Copy
Range("J3:IV" & Actions + 2).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End If
time2 = Format(Timer - time1, "00:00")
Calculate
Debug.Print time2
Application.ScreenUpdating = True

End Su

 

[dreamer]

Well, the whole code is a bit long so here is just the sub that does th
copying and pasting.

Sub Proces()
Application.ScreenUpdating = False
Dim ProcesID As Object
Dim time1 As Date
Dim time2 As String

time1 = Timer
PBSP.Select 'worksheet
Set ProcesID = PBSP.Range("A3:A1000")
Actions = Application.WorksheetFunction.CountA(ProcesID)
If Actions > 0 Then
Range("J1:IV1").Select
Selection.Copy
Range("J3:IV" & Actions + 2).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End If
time2 = Format(Timer - time1, "00:00")
Calculate
Debug.Print time2
Application.ScreenUpdating = True

End Su

 

[Harald Staff]

See if this is faster:

Sub test()
Dim ProcesID As Object
Dim time1 As Date
Dim time2 As String

time1 = Timer
PBSP.Select 'worksheet
Set ProcesID = PBSP.Range("A3:A1000")
Actions = Application.WorksheetFunction.CountA(ProcesID)
If Actions > 0 Then
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Range("J1:IV1").Copy Range("J3:IV" & Actions + 2)
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
time2 = Format(Timer - time1, "00:00")
Debug.Print time2
End If
End Sub

 

[dreamer]

Nope, it's still very slow

 

[Harald Staff]

Nope, it's still very slow.

My code is blistering fast here. Try it in a blank workbook.

Do you have any complex Change events running ? Try
Application.EnableEvents = False
along with the screenupdating settings. Set to True when done.