HELP- How can I start off excel with a new worksheet: sheet1, everytime?

[Acube]

Hi,
I created a excel workbook with many worksheets inside. But I would
like to reset(or delete all worksheets inside) the workbook and left
only with a new empty worksheet: Sheet1, everytime I re-start the
workbook. Any Macro code which I can use to do that??

Thanks alot

 

[Nigel]

The following is a generic process to create a new workbook with as many
sheets as required, place the number required in the function parameter,
e.g.
NewBook(1) for one sheet or NewBook(8) for eight sheets etc.

'============================================
' call the create function
' enter number of worksheets in new workbook
'============================================
Sub testwb()
If NewBook(1) Then
MsgBox "Workbook Created Successfully"
Else
MsgBox "Workbook Create Failed"
End If
End Sub

'============================================
' function to create a new workbook
' returns true if successful
'============================================
Function NewBook(xsheets As Integer) As Boolean

NewBook = False

' test within range
If xsheets >= 1 And xsheets <= 256 Then

' create new workbook
Dim NewWB As Workbook
Workbooks.Add
Set NewWB = ActiveWorkbook

' clean out the unwanted worksheets max= xsheets
Dim ws As Integer
With NewWB
For ws = .Sheets.Count To 1
If ws > xsheets Then Sheets(ws).Delete
Next
End With

' if not enough sheets add them
With NewWB
Do While .Sheets.Count < xsheets
.Sheets.Add after:=.Sheets(.Sheets.Count)
Loop
End With

' set return value
NewBook = True

End If

End Function