Selecting Last Worksheet

[Guest]

How would I select the last worksheet from a workbook?
I'm wanting to delete the last worksheet from a workbook.

 

[Ron de Bruin]

Hi Alex

Try this

Sub test()
With ThisWorkbook
If .Sheets.Count > 1 Then
Application.DisplayAlerts = False
.Sheets(.Sheets.Count).Delete
Application.DisplayAlerts = True
End If
End With
End Sub

 

[Guest]

Hmmmm...nothing happened.

Hi Alex

Try this

Sub test()
With ThisWorkbook
If .Sheets.Count > 1 Then
Application.DisplayAlerts = False
.Sheets(.Sheets.Count).Delete
Application.DisplayAlerts = True
End If
End With
End Sub

 

[Guest]

Ok I sorta figured it out. Though if anyone can tell me how to select the
last worksheet, I'd appreciate it.

Dim WS As Byte

WS = ActiveWorkbook.Worksheets.Count
Application.DisplayAlerts = False
ActiveWorkbook.Worksheets(WS).Delete
Application.DisplayAlerts = True

This works for the macro that it runs on, as the last sheet is always the
one that needs to be deleted, even if it's sort of a round about way to do
(or so I think) and this part of the code only runs once.

And to think I only spent all day trying to figure this out....

 

[Tom Ogilvy]

Sub test()
With ActiveWorkbook
If .WorkSheets.Count > 1 Then
Application.DisplayAlerts = False
.WorkSheets(.WorkSheets.Count).Delete
Application.DisplayAlerts = True
End If
End With
End Sub

 

[Chuck Elsham]

Hello folks,

It seems that there is a tangible difference between these 2 macros
that have been suggested:-

===============================

Sub test()
With ActiveWorkbook
If .WorkSheets.Count > 1 Then
Application.DisplayAlerts = False
.WorkSheets(.WorkSheets.Count).Delete
Application.DisplayAlerts = True
End If
End With
End Sub
================================

================================

What's the difference between ThisWorkbook and ActiveWorkbook please?

And between Worksheets and Sheets?

Many thanks
Rob

 

[Tom Ogilvy]

ThisWorkbook refers to the workbook that contains the code.

ActiveWorkbook is the workbook that has the focus in Excel's window.

They may be the same or not.

 

[Ron de Bruin]

And between Worksheets and Sheets?

And Sheets in my example will also delete a ChartSheet
Tom Change it to worksheets so it will only delete the last WorkSheet

If you only have worksheets in your workbook you can use both (Sheets or worksheets)