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


--
Jack of all trades... master of none..

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Alex" wrote in message ...


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


--
Jack of all trades... master of none..

Hmmmm...nothing happened.



"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Alex" wrote in message ...


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


--
Jack of all trades... master of none..

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

--
Regards,
Tom Ogilvy


"Alex" wrote in message
...
Hmmmm...nothing happened.



"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Alex" wrote in message
...


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


--
Jack of all trades... master of none..

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

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

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
================================

What's the difference between ThisWorkbook and ActiveWorkbook please?

And between Worksheets and Sheets?

Many thanks
Rob

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.

--
Regards,
Tom Ogilvy


"Chuck Elsham" wrote in message
...
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

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

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
================================

What's the difference between ThisWorkbook and ActiveWorkbook please?

And between Worksheets and Sheets?

Many thanks
Rob

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....

"Alex" wrote:



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


--
Jack of all trades... master of none..