removing worksheets with certain text in their name

dnickelson

Board Regular
Joined
Oct 30, 2003
Messages
118
Hello All,
Probably a simple question, but nothing coming up in searching the board.
I created some code to create several worksheets from a list that was captured from one of the other worksheets, but the code is set to be used over when the list changes, and I need an easy way to get rid of the sheets created previously. What I'm going for is basically

For Each Worksheet In Workbook
if worksheet.name contains "pc-" then
Delete Worksheet
Next

but not sure how to implement the if worksheet.name contains "pc-", VBA catches the syntax mistake before I even bother trying to run the code, but I'm not sure how to word it.

I'm assuming the above is enough of an explanation, but I'll expand if requested.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
dnickelson said:
Hello All,
Probably a simple question, but nothing coming up in searching the board.
I created some code to create several worksheets from a list that was captured from one of the other worksheets, but the code is set to be used over when the list changes, and I need an easy way to get rid of the sheets created previously. What I'm going for is basically

For Each Worksheet In Workbook
if worksheet.name contains "pc-" then
Delete Worksheet
Next

but not sure how to implement the if worksheet.name contains "pc-", VBA catches the syntax mistake before I even bother trying to run the code, but I'm not sure how to word it.

I'm assuming the above is enough of an explanation, but I'll expand if requested.

Try this, not tested though(I'm Not sure of the .delete method)

Dim ws as worksheet, wb as workbook
Set wb = Activeworkbook
Set Ws=Activesheet

for each ws in wb.worksheets
if left(ws.name,3) ="pc-" then ws.delete
next
 
Upvote 0
Hi,

Try:
Code:
Sub xxx()
Application.DisplayAlerts = False
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
If ws.Name Like "*pc-*" Then ws.Delete
Next ws
Application.DisplayAlerts = True
End Sub
 
Upvote 0
This Should do the trick for you

Greetings,

Monkey


Option Explicit

Sub Monkey()
Dim X As Integer
Dim Text As String
Text = "PC-"

For X = 1 To sheets.Count
If InStr(1, sheets(X).Name, "PC-") Then sheets(X).Delete
Next X
End Sub
 
Upvote 0
Not tested ...

Code:
Dim Ws As Worksheet
Application.DisplayAlerts = False
For Each Ws In Worksheets 
   If Left(Ws.Name, 3) = "pc-" Then Ws.Delete 
Next 
Application.DisplayAlerts = True
 
Upvote 0
Slick. I used the second suggestion (mainly because the DisplayAlerts snippet was something I realized I might have to deal with only after I saw it) but all three were the syntax examples I was looking for. thanks much for the quick responses.
 
Upvote 0
Assuming that the sheet name BEGINS with PC-


Code:
Sub delpc()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Sheets
        If UCase(Left(ws.Name, 3)) = "PC-" Then
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
        End If
    Next
End Sub

If the PC- can be anyplace in the sheet name replace

If UCase(Left(ws.Name, 3)) = "PC-" Then

with

If InStr(1, UCase(ws.Name), ("PC-")) Then


HTH
 
Upvote 0

Forum statistics

Threads
1,214,685
Messages
6,120,876
Members
448,993
Latest member
InquisitiveFrog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top