What's the best way to get the last non-empty worksheet in Excel (VBA)

Question

I'm trying to write a VBA macro for a group that has one workbook where they daily create new worksheets, but they also have Sheet 1, Sheet 2, Sheet 3 at the end of their long list of sheets. I need to create a external cell reference in a new column in a different workbook where this information is being summarized, so I need to know how to get the last non-empty sheet so I can grab this data and place it appropriately in the summary.

Answer 1

This function works through the sheets from right to left until it finds a non-blank sheet, and returns its name

Function GetLastNonEmptySheetName() As String
Dim i As Long
For i = Worksheets.Count To 1 Step -1
  If Sheets(i).UsedRange.Cells.Count > 1 Then
    GetLastNonEmptySheetName = Sheets(i).Name
    Exit Function
  End If
Next i
End Function