sukumar.vb
08-28-2011, 07:22 PM
Please refer to remarks in this program: -
Private Sub Ab() 'Workbook_BeforeClose(Cancel As Boolean)
Dim wswork As Worksheet
Dim hcount As Integer
hcount = 1
' first step to activate hello worksheet, if it exists
For Each wswork In ThisWorkbook.Worksheets
If wswork.Name <> "hello" Then
hcount = 0
End If
Next wswork
' Problem exists here, when there is another sheet, say sheet3, along with hello sheet.
'Then hcount = 0 will lead to error # 1004, perhaps.
'I am looking to have good program without Onerror and Goto statement.
' Please refer attachment. Please remove these Remarks in your reply.
MsgBox hcount
If hcount = 1 Then
Sheets("hello").Activate
' second step to create hello worksheet, if it does not exists
Else
Worksheets(1).Name = "hello"
End If
' third step to delete remaining worksheets except hello worksheet
Application.DisplayAlerts = False
For Each wswork In ThisWorkbook.Worksheets
If wswork.Name <> "hello" Then wswork.Delete
Next wswork
End Sub
Private Sub Ab() 'Workbook_BeforeClose(Cancel As Boolean)
Dim wswork As Worksheet
Dim hcount As Integer
hcount = 1
' first step to activate hello worksheet, if it exists
For Each wswork In ThisWorkbook.Worksheets
If wswork.Name <> "hello" Then
hcount = 0
End If
Next wswork
' Problem exists here, when there is another sheet, say sheet3, along with hello sheet.
'Then hcount = 0 will lead to error # 1004, perhaps.
'I am looking to have good program without Onerror and Goto statement.
' Please refer attachment. Please remove these Remarks in your reply.
MsgBox hcount
If hcount = 1 Then
Sheets("hello").Activate
' second step to create hello worksheet, if it does not exists
Else
Worksheets(1).Name = "hello"
End If
' third step to delete remaining worksheets except hello worksheet
Application.DisplayAlerts = False
For Each wswork In ThisWorkbook.Worksheets
If wswork.Name <> "hello" Then wswork.Delete
Next wswork
End Sub