Calculation slowing macro (COUNTIFS)

[Dancey_]

Morning guys,

I hope someone can help me. I am designing a sales database for the company I work for.

I have an input sheet (Sales Input.xlsm) that when a user hits save sale uses the following code to copy the information to the "Sales" sheet of my 'database':

Sub Save()
Application.ScreenUpdating = False
If Range("C2") = "" Then
MsgBox "Please ensure you fill in 'Agent' then try again!"
Else
If Range("D2") = "" Then
MsgBox "Please ensure you fill in 'Policy Number' then try again!"
Else
If Range("E2") = "" Then
MsgBox "Please ensure you fill in 'Type' then try again!"
Else
If Range("F2") = "" Then
MsgBox "Please ensure you fill in 'Source' then try again!"
Else
If Range("G2") = "" Then
MsgBox "Please ensure you fill in 'Quote £' then try again!"
Else
If Range("H2") = "" Then
MsgBox "Please ensure you fill in 'Sale £' then try again!"
Else
If MsgBox("Are all the details you have entered correct?", vbYesNo + vbQuestion) = vbYes Then
Workbooks.Open "C:\Documents and Settings\tom.dance\Desktop\Sales Database.xlsm"
ThisWorkbook.Sheets("Sheet1").Range("A2:M2").Copy
Application.Calculation = xlCalculationManual
Workbooks("Sales Database.xlsm").Sheets("Sales").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Workbooks("Sales Database.xlsm").Save
Workbooks("Sales Database.xlsm").Close
ThisWorkbook.Sheets("Sheet1").Range("C2:M2").ClearContents
End If
End If
End If
End If
End If
End If
End If
End Sub

Technically it works perfectly, but now that I am building up the reporting side of things on "Sales Database.xlsm" I have about a hundred COUNTIFS' per agent, so far only two agents have been input, but there will about twenty.

Before I added in the COUNTIFS' when I executed the above code it would update almost instantly and that was perfect. However, now whenever "Sales Database.xlsm" is opened by the macro and the data is copied across it takes a good 5-6 seconds which is too long as the agents will be doing this repeatedly throughout the day.

I have added the following to "Sales Database.xlsm":

Private Sub Workbook_Open()
Application.Calculation = xlCalculationManual
End Sub

Which is perfect when you manually open the spreadsheet as it stops it calculating until you tell it to. However, it hasn't affected the macro running when a sale is saved.

If possible I would like to keep the COUNTIFS' instead of using something like DCOUNT, I did have it setup on DCOUNT but then I ended up with a hidden sheet with a reference set of cells to tell it what to look for which had about 1200 reference cells on it. (I may have been using the DCOUNT wrong but I was setting up a 2x2 cell section for each formulae which had the two table heading I wanted to look at and the date and the agent name, to tell the formulae where to look in the database. Is that right?)

Thanks in advance!

Cheers

 

[Andrew Poulsom]

Is the CalculateBeforeSave property set to False?

 

[Dancey_]

Is the CalculateBeforeSave property set to False?

No, I haven't done that.

Would that be:

Calculate.BeforeSave = False

Cheers

 

[Dancey_]

No, I haven't done that.

Would that be:

Calculate.BeforeSave = False

Cheers

Application.CalculateBeforeSave = False

Has worked perfectly!

Thanks