Macros Running Slow in a Shared workbook

M

MichaelLove

This is one of the Macro's that runs extremely slow in my
workbook when it is in shared format.

Sub ProductsUpdate()

Application.ScreenUpdating = False

Dim myRng As Range
Dim lastRw As Long
'get the last row with an entry

If ActiveSheet.[B11] = 0 Then
ActiveSheet.[B11].Formula = "1"
End If

ActiveSheet.[B9:B10].Formula = "1"
lastRw = ActiveSheet.[B9].End(xlDown).Row
ActiveSheet.[B12:AS400].Sort Key1:=Range("B12"),
Order1:=xlDescending, Orientation:=xlTopToBottom
With ActiveSheet.[BF11]
.Formula = "=IF(AND($B11>=$AT$1,$B11<=$AU$1),L11,)"
.AutoFill Destination:=ActiveSheet.[BF11:CM11]
End With
With ActiveSheet.[CN11]
.Formula = "=IF(AND($B11>=$AT$1,$B11<=$AU$1),D11,)"
.AutoFill Destination:=ActiveSheet.[CN11:CU11]
End With

ActiveSheet.[BF11:CU11].Copy
Destination:=ActiveSheet.Range("BF12:BF" & lastRw&)
ActiveSheet.[BF6:CU6].Copy
ActiveSheet.[BF7].PasteSpecial Paste:=xlValues
ActiveSheet.Range("BF11:CU" & lastRw&).ClearContents
Application.CutCopyMode = False


End Sub
 
F

Frank Kabel

Hi Michael
probably not much you can do about it (you may set the calculation mode
to 'manual' at the beginning of your macro and to 'automatic' at the
end of your macro).
But I assume that this is a problem of shared workbooks

--
Regards
Frank Kabel
Frankfurt, Germany

This is one of the Macro's that runs extremely slow in my
workbook when it is in shared format.

Sub ProductsUpdate()

Application.ScreenUpdating = False

Dim myRng As Range
Dim lastRw As Long
'get the last row with an entry

If ActiveSheet.[B11] = 0 Then
ActiveSheet.[B11].Formula = "1"
End If

ActiveSheet.[B9:B10].Formula = "1"
lastRw = ActiveSheet.[B9].End(xlDown).Row
ActiveSheet.[B12:AS400].Sort Key1:=Range("B12"),
Order1:=xlDescending, Orientation:=xlTopToBottom
With ActiveSheet.[BF11]
.Formula = "=IF(AND($B11>=$AT$1,$B11<=$AU$1),L11,)"
.AutoFill Destination:=ActiveSheet.[BF11:CM11]
End With
With ActiveSheet.[CN11]
.Formula = "=IF(AND($B11>=$AT$1,$B11<=$AU$1),D11,)"
.AutoFill Destination:=ActiveSheet.[CN11:CU11]
End With

ActiveSheet.[BF11:CU11].Copy
Destination:=ActiveSheet.Range("BF12:BF" & lastRw&)
ActiveSheet.[BF6:CU6].Copy
ActiveSheet.[BF7].PasteSpecial Paste:=xlValues
ActiveSheet.Range("BF11:CU" & lastRw&).ClearContents
Application.CutCopyMode = False


End Sub
-----Original Message-----
Hi
no way to 'fool' the system. You may post the relevant part of your
code which runs so slow

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag news:01F13054-E32A-40CE-B6E2- [email protected]... form it causes
my system to run extremely slow. If the workbook is not in shared
form the Macros run faster and my system doesn't freeze. Is there a
way to fool the system into thinking the workbook is not in shared
form which may help it run faster?
Click to expand...
Click to expand...
 
Top