Please use CamelCase in your code.
osht is declared as a collection of Sheets
Try using LookIn:=xlValues
Your code will have to be updated every leap year and the next.
Check out these UDF's. They all assume that "D1" and Column "A" are formatted as Date
Function BOM(dDate As Date) As Date
'Beginning Of Month
Dim iDay As String 'For international use
Dim iMonth As Integer
Dim iYear As Integer
Dim sSeparator As String
sSeparator = "/"
iDay = "1"
iMonth = CStr(Month(dDate))
iYear = CStr(Year(dDate))
BOM = CDate(iMonth & sSeparator & iDay & sSeparator & iYear)
End Function
Function BOY(dDate As Date) As Date
'Beginning Of Year
Dim sYear As String
sYear = CStr(Year(dDate))
BOY = CDate("1/1/" & sYear) 'Already international
End Function
Function Last30Days(dDate As String) As Date
'The Date 30 days ago. March 1st = Jan 29; Jan 31 = Jan 1st
Last30Days = dDate - 30
End Function
Function PreviousMonth(dDate As Date) As Date
'Returns proper date of previous month. Mar 1st = Feb 1st; Mar30 = Feb 28 (29 on leap years)
PreviousMonth = DateAdd(m, -1, dDate)
End Function
Since you are wanting to SUM the values in various Ranges, and since I have no idea what your columns are, I will use XXX for any and all column references.
To Sum only this Month To Date use the above BOM function
Sub Test()
Dim TotalThisMonthToDate
Dim StartRow As Long
Dim EndRow As Long
Const XXX As Long = 4 'Random column number
With Sheets("YTD")
StartRow = .Range("A:A").Find(BOM(.Range("D1")), LookIn:=xlValues).Row
EndRow = .Range("A:A").Find(.Range("D1").Value, LookIn:=xlValues).Row
TotalThisMonthToDate = WorksheetFunction.Sum(Range(Cells(StartRow, XXX), Cells(EndRow, XXX)))
End With
End Sub
For totals spanning months (and their subtotals,) (Function PreviousMonth or Function Last30Days,) (assuming you did not use the Subtotals function to create those subtotals,) you will need to use a Loop and use.
For Each Cel in Range(Cells(StartRow, XXX), Cells(EndRow, XXX))
If TypeName(Cells(Cel.Row, "A")) = "Date" Then Total = Total + Cel.Value
If you used the Subtotal function to get the monthly totals in the Database, you can use Subtotal the same way Sum is used in Sub Test above.