select and delete sheets
Hello! I am new here.. have recently got into excel+vba. I have searched firs to see if I found an answer to my question.. but so far: nothing. S sorry if I just didn't search enough and this question has appeare before! I want to write a macro that selects all the sheets in a workboo EXCEPT the active one (which can be ANY of the sheets) and delete them, so I don't have to go manually and select them one by one. sometimes the files have just 3 sheets, but others they have over 40. This is what I wrote so far, Sub DeleteSheets() Dim a, i, NrWs as Integer a = Workbooks.Count NrWs = Worksheets.Count For i = 1 To NrWs Workbooks(a).Worksheets(i).Select ActiveWindow.SelectedSheets.Delete Next i End Sub When I run this macro, it deletes 3 of the sheets, but when it reache the 4th I get the following error: Run-Time Error '9': Subscript out of range So I don't know what to do from here :( Hope sb can help!! Thanks a lot! / ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
select and delete sheets
Try this
Sub test() For Each sh In ThisWorkbook.Sheets If sh.Name < ActiveSheet.Name Then Application.DisplayAlerts = False sh.Delete Application.DisplayAlerts = True End If Next sh End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Locachica" wrote in message ... Hello! I am new here.. have recently got into excel+vba. I have searched first to see if I found an answer to my question.. but so far: nothing. So sorry if I just didn't search enough and this question has appeared before! I want to write a macro that selects all the sheets in a workbook EXCEPT the active one (which can be ANY of the sheets) and deletes them, so I don't have to go manually and select them one by one.. sometimes the files have just 3 sheets, but others they have over 40.. This is what I wrote so far, Sub DeleteSheets() Dim a, i, NrWs as Integer a = Workbooks.Count NrWs = Worksheets.Count For i = 1 To NrWs Workbooks(a).Worksheets(i).Select ActiveWindow.SelectedSheets.Delete Next i End Sub When I run this macro, it deletes 3 of the sheets, but when it reaches the 4th I get the following error: Run-Time Error '9': Subscript out of range So I don't know what to do from here :( Hope sb can help!! Thanks a lot! /J ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
select and delete sheets
Try something like the following
Dim WS As Worksheet Application.DisplayAlerts = False Application.ScreenUpdating = False For Each WS In Worksheets If WS.Name < ActiveSheet.Name Then WS.Delete End If Next WS Application.DisplayAlerts = True Application.ScreenUpdating = True -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Locachica" wrote in message ... Hello! I am new here.. have recently got into excel+vba. I have searched first to see if I found an answer to my question.. but so far: nothing. So sorry if I just didn't search enough and this question has appeared before! I want to write a macro that selects all the sheets in a workbook EXCEPT the active one (which can be ANY of the sheets) and deletes them, so I don't have to go manually and select them one by one.. sometimes the files have just 3 sheets, but others they have over 40.. This is what I wrote so far, Sub DeleteSheets() Dim a, i, NrWs as Integer a = Workbooks.Count NrWs = Worksheets.Count For i = 1 To NrWs Workbooks(a).Worksheets(i).Select ActiveWindow.SelectedSheets.Delete Next i End Sub When I run this macro, it deletes 3 of the sheets, but when it reaches the 4th I get the following error: Run-Time Error '9': Subscript out of range So I don't know what to do from here :( Hope sb can help!! Thanks a lot! /J ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
select and delete sheets
thanks a lot!! it worked prefectly : ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
select and delete sheets
First of all you need to do it backwards, you also need
to save the active sheets name/index, you don't want to delete that.Try this Sub DeleteSheets() Dim a, i, NrWs As Integer 'Save the activesheets name AWs = ActiveSheet.Name Debug.Print AWs a = Workbooks.Count NrWs = Worksheets.Count For i = NrWs To 1 Step -1 If Worksheets(i).Name < AWs Then Debug.Print NrWs, i Workbooks(a).Worksheets(i).Select ActiveWindow.SelectedSheets.Delete End If Next i End Sub Regards Lars Kofod -----Original Message----- Hello! I am new here.. have recently got into excel+vba. I have searched first to see if I found an answer to my question.. but so far: nothing. So sorry if I just didn't search enough and this question has appeared before! I want to write a macro that selects all the sheets in a workbook EXCEPT the active one (which can be ANY of the sheets) and deletes them, so I don't have to go manually and select them one by one.. sometimes the files have just 3 sheets, but others they have over 40.. This is what I wrote so far, Sub DeleteSheets() Dim a, i, NrWs as Integer a = Workbooks.Count NrWs = Worksheets.Count For i = 1 To NrWs Workbooks(a).Worksheets(i).Select ActiveWindow.SelectedSheets.Delete Next i End Sub When I run this macro, it deletes 3 of the sheets, but when it reaches the 4th I get the following error: Run-Time Error '9': Subscript out of range So I don't know what to do from here :( Hope sb can help!! Thanks a lot! /J ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ . |
select and delete sheets
Thanks for that. You answered my question "How do I get VB to delete an
Excel worksheet without asking first". Simon *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
All times are GMT +1. The time now is 10:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com