Sum a range based on other range criteria for all sheets

[arijitirf]

Hi!!!!

Sub getSum()
Dim strDate As String
Dim ws As Worksheet
strDate = InputBox("Insert date in format mm/yyyy", "User date", Format(Now(), "mm/yyyy"))
If IsDate(strDate) Then
strDate = Format(CDate(strDate), "mm/yyyy")
Else
MsgBox "Wrong date format. Please try again."
Exit Sub
End If
Dim ldateto As Long
Dim ldatefrom As Long
Dim LastRow As Long
Dim ThisMonth As Integer
Dim ThisYear As Long
Dim qty As Long
ThisMonth = Month(strDate)
ThisYear = Year(strDate)
ldatefrom = DateSerial(ThisYear, ThisMonth, 1)
ldateto = DateSerial(ThisYear, ThisMonth + 1, 0)
For Each ws In Sheets
LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
ws.Range("E2:E" & LastRow).AutoFilter Field:=1, Operator:=xlFilterValues, Criteria1:=">=" & ldatefrom, Operator:=xlAnd, Criteria2:="<=" & ldateto
qty = qty + WorksheetFunction.Sum(ws.Range("G2:G" & LastRow).SpecialCells(xlCellTypeVisible))
If ws.AutoFilterMode = True Then ws.AutoFilterMode = False
Next ws
If qty = 0 Then
MsgBox ("There is no data for " & MonthName(ThisMonth) & ".")
Else
MsgBox ("The sum of values for " & MonthName(ThisMonth) & "/" & ThisYear & " is " & qty & ".")
End If
End Sub


Above code was made by Mumps, member in this forum. This code is working fine. But due to auto filter activation it takes plenty of times to calculate results for 1000+ sheets.

Is there any other code so that it can calculate faster.

Thanks in advance....