Inconsistent error in code

[Sarah]

All,

I have some code that exports resource availability to Excel and
formats an Out of Office report. This code works consistently when the
date range I enter is small (5 or 10 days). But when I try to do a
whole calendar month, it doesn't consistently work. Sometimes it's
fine, but sometimes it fails. And it's always on the same line of
code:

appXL.Range("B2", RealLastCell(Sheets("Sheet1"))).Select

I get a run-time error 1004, "Method 'Sheets' of object '_Global'
failed".

The statement refers to a function:

Function RealLastCell(TheSheet As Worksheet) As Range
Set ExcelLastCell = TheSheet.Cells.SpecialCells(xlLastCell)

LastRowWithData = ExcelLastCell.Row
Row = ExcelLastCell.Row
Do While Excel.Application.CountA(TheSheet.Rows(Row)) = 0 And Row
<> 1
Row = Row - 1
Loop
LastRowWithData = Row

LastColWithData = ExcelLastCell.Column
Col = ExcelLastCell.Column
Do While Excel.Application.CountA(TheSheet.Columns(Col)) = 0 And
Col <> 1
Col = Col - 1
Loop
LastColWithData = Col

Set RealLastCell = TheSheet.Cells(Row, Col)
End Function

Does anyone have any ideas? Let me know if you need more information
than this. I didn't want to copy the whole module into this message.

Thanks!
Sarah
sarah_kiko@(removethis)cinfin.com