PDA

View Full Version : Solved: Worksheet Problem



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

sukumar.vb
08-28-2011, 07:37 PM
I got a solution: -



Private Workbook_BeforeClose(Cancel As Boolean)
Dim wswork As Worksheet
Dim hcount As Integer

hcount = 0
' first step to activate hello worksheet, if it exists
For Each wswork In ThisWorkbook.Worksheets
If wswork.Name = "hello" Then
hcount = 1
End If
Next wswork

'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




I will welcome different program.