CODEWITHSUNDEEP
excelvba
user2151190
user2151190

Reputation: 189

How to erase sheet when partial name is written is sheetname?

How can I tell with VBA that if the name of the worksheet contains the name "name" of part of the word "name" to completely erase that sheet please??

When the sheet is ok , there is a normal name written in the sheetname

When the sheet is blanc that the sheets are called "name 1" or "name 2" etc till "name 30"

I have to remove the blanc sheets.

Below my attempt, but not working.

Sub erasesheet(wbNew As Workbook)   
Dim ws As Worksheet

For Each ws In Worksheets

'here I have to be able to tell : I it contains the word "name" in the sheet
'like the code is now it will only look exactly fot sheets with name "name"
      If Sheets.Name = "name"   Then
         Application.DisplayAlerts = False
         ws.Delete
      Application.DisplayAlerts = True
     End If
  Next ws
end sub

Upvotes: 0

Views: 116

Answers (1)

Pankaj Jaju
Pankaj Jaju

Reputation: 5481

You need to use Instr. Try the following code

Sub erasesheet(wbnew As Workbook)
    Dim ws As Worksheet

    For Each ws In wbnew.Worksheets
        If InStr(1, UCase(ws.Name), "NAME") Then
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
        End If
      Next ws
End Sub

Upvotes: 2

Related Questions