removing worksheets with certain text in their name

[dnickelson]

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.

 

[Cbrine]

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

 

[fairwinds]

Hi,

Try:

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

 

[Monkey]

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

 

[Andrew Poulsom]

Not tested ...

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

 

[dnickelson]

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.

 

[TryingToLearn]

Assuming that the sheet name BEGINS with PC-

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