Sum the Total value of a certain range with date for whole workbook

[arijitirf]

Hi!!!!

My workbook contains more that 1000 sheets and increases daily. I need to get total value of column "I" (Range "I7:I") for all sheets based on month in column "F" (Range "F7:F").

mumps (Member in this forum) have given me this wonderful code mentioned below which is working fine.

But as I am using a workbook that contains more than 1000 sheets, so it takes plenty of times to show the result due to filtering process. Sometimes it hangs due to poor RAM.

Is there any other possible tricks in vba to get rid of this...

Thanks in advance.

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

 

[Dryver14]

Are all the sheets changing every month? if not why not total say the first 800 then calculate forward from there

 

[arijitirf]

Yes, almost all the sheet are changing every month along with new sheet creation.

 

[Dryver14]

the only other thing I can think of is to put a set sum function way below where the data is expected to finished, for instance row 1000 then sum across sheets