I think that the problem  is that when it sets up the for each sheet  it
identifies that there are, say 7 sheets. When it deletes one it does not
alter this number. so it eventually gets to a non-existent sheet.

Try doing it backwards

something like (untested code)

for x = worksheets.count to 1 step -1
With worksheets(x)
If IsDate(.Name) Then
If DateValue(.Name) <= DateValue(Date - 14) Then
Application.DisplayAlerts = False
.Delete
End If

End If


Next x