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.
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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
 

Monkey

Active Member
Joined
Feb 10, 2003
Messages
251
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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
 

dnickelson

Board Regular
Joined
Oct 30, 2003
Messages
118
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

Well-known Member
Joined
Sep 10, 2003
Messages
723
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,480
Messages
5,601,911
Members
414,482
Latest member
morkar

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
Top