|
Selecting Last Worksheet
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.. |
Selecting Last Worksheet
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.. |
Selecting Last Worksheet
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.. |
Selecting Last Worksheet
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.. |
Selecting Last Worksheet
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.. |
Selecting Last Worksheet
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 |
Selecting Last Worksheet
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 |
Selecting Last Worksheet
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) -- Regards Ron de Bruin http://www.rondebruin.nl "Tom Ogilvy" wrote in message ... 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 |
| All times are GMT +1. The time now is 06:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com