[RESOLVED] Need to delete some worksheets and save others
I am new to VB, but fortunately you guys/gals have been extremely helpful.
I am working on a macro for excel. When played in a workbook I want the macro to ignore three worksheets that I am saving “MM”, “JE”, and “JO” but delete the rest. The worksheets that need to be deleted vary in amount and name.
Re: Need to delete some worksheets and save others
If the worksheets that you are wishing to save never change then this can be achieved by looping around the sheets collection and deleting sheets that do not match criteria.
Two ways..
Example 1 - Deleting as you go
VB Code:
Dim i As Integer
DisplayAlerts = False
LoopReEnter:
For i = 1 to Sheets.Count
If Sheets(i).Name <> "MM" Or Sheets(i).Name <> "JE" Or Sheets(i).Name <> "JO" Then
Sheets(i).Delete
Goto LoopReEnter
End If
Next i
DisplayAlerts = True
Example 2 - Selecting then Deleting
VB Code:
Dim i As Integer
DisplayAlerts = False
For i = 1 to Sheets.Count
If Sheets(i).Name <> "MM" Or Sheets(i).Name <> "JE" Or Sheets(i).Name <> "JO" Then
Sheets(i).Select
End If
Next i
Selection.Delete
DisplayAlerts = True
Re: Need to delete some worksheets and save others
In your macro code do a test for the sheet name and if its one of the three then skip deleting.
VB Code:
Dim i As Integer
Application.DisplayAlerts = False
For i = 1 To Sheets.Count
If Sheets(i).Name = "MM" Or Sheets(i).Name = "JE" Or Sheets(i).Name = "JO" Then
'Skip
Else
Sheets(i).Delete
End If
Next
Application.DisplayAlerts = True
Re: Need to delete some worksheets and save others
Re: Need to delete some worksheets and save others
Its easier to iterate backwards through your sheets this way you won't get into trouble due to reindexing sheets in a sheet deletion. ( the sheet after the sheet you delete will get the index of the deleted sheet)
I hope it makes sence to you :)
Code:
Dim i As Integer
DisplayAlerts = False
For i = Sheets.Count to 1 step -1
If Sheets(i).Name <> "MM" Or Sheets(i).Name <> "JE" Or Sheets(i).Name <> "JO" Then
Sheets(i).Delete
End If
Next i
DisplayAlerts = True
Re: Need to delete some worksheets and save others
Anymore for anymore?? :D
BTW Dnereb.. you following me?? and I also covered that off in my first example.. notice the goto statement :D
Re: Need to delete some worksheets and save others
Yes, I forgot the reverse looping. Oh well. Easy enough to take care of though.
You can "win" Danny although it not a competition. Its just helping out members with their issues. ;)
Re: Need to delete some worksheets and save others
It could be a problem on my end but,
Danny, I played with both of your sets of code, the first deletes all of the worksheets, the second deletes a cell if a cell is selected then moves to the end worksheet and stops.
RobDog, your code does save and delete the correct worksheets, but goes into debug mode depending on the position of the saved and deleted worksheets. Also, what code did you have in mind for skipping?
Dnereb like Danny your code deletes all of the worksheets.
Re: Need to delete some worksheets and save others
Sorry second example should be..
VB Code:
Dim i As Integer
DisplayAlerts = False
For i = 1 to Sheets.Count
If Sheets(i).Name <> "MM" Or Sheets(i).Name <> "JE" Or Sheets(i).Name <> "JO" Then
Sheets(i).Select
End If
Next i
Selection.Delete
ActiveWindow.SelectedSheets.Delete
DisplayAlerts = True
My First example should of worked fine as it is just the reverse of RD's, it should only delete the sheet if the name does not match..
RD
I know just kidding..
Re: Need to delete some worksheets and save others
Yes, as I mentioned in my last post. You need to reverse the loop by...
VB Code:
For i = Sheets.Count To 1 Step -1
Ps, Danny, I know you are but others may not. ;)
Re: Need to delete some worksheets and save others
Thanks for the quick reply, but this time it deleted the end sheet which happened to be "JE" then stopped. Perhaps it is something on my end; but I have tripled checked the names, and the sheets to be deleted are just inserted sheets that I am using to hammer on.
Re: Need to delete some worksheets and save others
I reversed Rob Dog's loop and now it works like a charm. Thank much!
Re: Need to delete some worksheets and save others
Post the code your using so we are all on the same page. ;)
Re: Need to delete some worksheets and save others
VB Code:
Dim i As Integer
Application.DisplayAlerts = False
For i = Sheets.Count To 1 Step -1
If Sheets(i).Name = "MM" Or Sheets(i).Name = "JE" Or Sheets(i).Name = "JO" Then
'Skip
Else
Sheets(i).Delete
End If
Next
Application.DisplayAlerts = True
Set ws = Nothing