How to find the last sheet in a workbook using VBA?

To find the last sheet in a workbook using VBA, you can use the following approach. The VBA code provided below helps you identify the last sheet in terms of the index number within the workbook:

Sub FindLastSheet()
    Dim wb As Workbook
    Dim lastSheet As Worksheet
    
    ' Assuming you want to work with the active workbook
    Set wb = ActiveWorkbook
    
    ' Set lastSheet to the last worksheet in the workbook
    Set lastSheet = wb.Sheets(wb.Sheets.Count)
    
    ' Output the name of the last sheet
    MsgBox "The last sheet in the workbook is: " & lastSheet.Name
End Sub

Explanation:

Workbook and Worksheet Objects: The code uses `Workbook` and `Worksheet` objects to reference parts of the workbook.
Set wb: The code assumes you are working with the active workbook (`ActiveWorkbook`). If you wish to specify a different workbook, you can replace `ActiveWorkbook` with `Workbooks(“YourWorkbookName”)` where “YourWorkbookName” is the name of the target workbook.
wb.Sheets.Count: This line retrieves the number of sheets within the workbook, and therefore identifies the index of the last sheet.
Set lastSheet: Once the last sheet index is known, `wb.Sheets(wb.Sheets.Count)` accesses the last sheet in the collection.
MsgBox: This line displays a message box with the name of the last sheet.

When you run this macro, it will display a message box showing the name of the last sheet in the currently active workbook. You can modify the output or further manipulate the last sheet as needed.

facebooklinkedin