Deleting multiple worksheets and saving active worksheet

[singcbl]

I am trying to find a easy way to delete multiple worksheets in a workbook and then saving the workbook based on the tab name of the worksheet.

 

[singcbl]

Jindon-san,

This error message appeared at this position

Method 'Delete object'_Worksheet' failed

If ws.Name <> NewName Then ws.Delete

 

[singcbl]

Jindon-san,

Could this be due to there are hidden sheets.

 

[jindon]

It is not selecting/activating the object, so it shouldn't be the issue.

Isn't the workbook protected?

 

[singcbl]

Jindon-san,

Yes, it is protected but when I modify your codes as follows it seem to work

Dim myPath As String, NewName As String, ws As Worksheet
myPath = ThisWorkbook.Path
NewName = ActiveSheet.Name
Application.DisplayAlerts = False
For Each ws In Sheets
ws.Visible = True
     If ws.Name <> NewName Then ws.Delete
Next
ThisWorkbook.SaveAs NewName & ".xls"

 

[singcbl]

Jindon-san,

I have to correct my statement. I need to have the hidden sheets left hidden because I need these sheets as they are linked to the active sheet. If this the case how to change the code to overcome the error

 

[jindon]

Hummm

OK.

 

[singcbl]

Jindon,

Of the hidden sheets, I just need to make the sheet "Template" visible and delete with the rest of the sheets except the active sheet

 

[singcbl]

Jindon-san,

Finally found a way round the problem of hidden files. I managed to find the solution and change the codes as follows.

Sub exportwksht()
    Dim myPath As String, NewName As String, ws As Worksheet
    myPath = ThisWorkbook.Path
    NewName = ActiveSheet.Name
    Application.DisplayAlerts = False
    For Each ws In Sheets
    On Error Resume Next
    If ws.Name <> NewName Then ws.Delete
    Next ws
    On Error GoTo 0
    Application.DisplayAlerts = True
    ThisWorkbook.SaveAs NewName & ".xls"
End Sub