-
Mar 4th, 2018, 04:13 AM
#1
Thread Starter
Hyperactive Member
[RESOLVED] Delete all worksheets except few specified worksheets from a workbook
I have got the below macro to delete all worksheets in a workbook except few specified ones, but it shows type mismatch error on the line highlighted in bold. I am using excel 2010 version.
Code:
Sub deleteshts ()
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Sheet3" Or "Sheet8" Or "Sheet11" Or "Pivot" Then
ws.Delete
End If
Next
Application.DisplayAlerts = True
End Sub
-
Mar 4th, 2018, 06:01 AM
#2
Re: Delete all worksheets except few specified worksheets from a workbook
Code:
Sub delSheets()
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "pivot" And ws.Name <> "Sheet3" Then 'add more as needed
ws.Delete
End If
Next
Application.DisplayAlerts = True
End Sub
-
Mar 4th, 2018, 06:21 AM
#3
Re: Delete all worksheets except few specified worksheets from a workbook
of you could use a select case
Code:
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "sheet3", "pivot", "sheet8" ' add as required
' do nothing
Case Else
ws.Delete
End Select
Next
Application.DisplayAlerts = True
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Mar 4th, 2018, 07:27 AM
#4
Thread Starter
Hyperactive Member
Re: Delete all worksheets except few specified worksheets from a workbook
Great. It works. thanks a lot
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|