Hello All..

I am trying to get this thing to function..without much success.

What it needs to do is..

Only insert one new sheet on request (called from another macro/userform)

Check if that sheet already exists, then insert the next one from the array
where the sheet names are stored.

Here's the code so far..(It just spits out all the sheets in the array in
one go)


Sub Newsheets()

Dim nLast As Long
Dim i As Long
Dim NewSheet
Dim n As Long
Dim a As Long

NewSheet = Array("31-60", "61-90", "91-120", "121-150")

For n = LBound(NewSheet) To UBound(NewSheet)
nLast = Sheets.Count
For i = 1 To ActiveWindow.SelectedSheets.Count


Sheets.Add _
After:=Sheets(Sheets.Count), _
Type:= _
"C:\Documents and Settings\Admin\Application
Data\Microsoft\Templates\New Sheet.XLT"
Sheets("sheet1").Name = NewSheet(n)
Count = 1

Sheets(nLast + 1).Select

Next i
Add_Numbers
Next n


End Sub

Any thoughts on where to go from here are indeed welcomed.

Regards
Peter

Peter,

Here is my version of your code.
The "Type" designation is commented out so the
code would work on my machine.
Same for the Call to Add_Numbers...

'=============================
Sub Newsheets()
Dim n As Long
Dim NewSheet As Variant

NewSheet = _
Array("31-60", "61-90", "91-120", "121-150")

For n = LBound(NewSheet) To UBound(NewSheet)
If WorksheetExists(NewSheet(n)) = False Then
Exit For
End If
Next 'n
If n UBound(NewSheet) Then
MsgBox "No more sheets to add. "
Exit Sub
Else
With Worksheets.Add(After:=Worksheets(Worksheets.Count) , _
Count:=1, Type:=xlWorksheet)

' "C:\Documents and Settings\Admin\Application Data\" & _
' "Microsoft\Templates\New Sheet.XLT"

.Name = NewSheet(n)
End With
End If
'Add_NumbersFunction
End Sub

'-------------------------------------------
' Function by Chip Pearson 04/20/2003
' Check if Worksheet name exists
' 1st argument passed ByVal to allow use of variants - (Jim Cone)

Function WorksheetExists(ByVal WSName As String, Optional WB As Workbook = Nothing) As Boolean
On Error Resume Next
WorksheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook, WB).Worksheets(WSName).Name))
End Function
'======================================

Regards,
Jim Cone
San Francisco, USA



"Peter" wrote in message
...
Hello All..

I am trying to get this thing to function..without much success.
What it needs to do is..
Only insert one new sheet on request (called from another macro/userform)
Check if that sheet already exists, then insert the next one from the array
where the sheet names are stored.
Here's the code so far..(It just spits out all the sheets in the array in
one go)

Sub Newsheets()

Dim nLast As Long
Dim i As Long
Dim NewSheet
Dim n As Long
Dim a As Long
NewSheet = Array("31-60", "61-90", "91-120", "121-150")

For n = LBound(NewSheet) To UBound(NewSheet)
nLast = Sheets.Count
For i = 1 To ActiveWindow.SelectedSheets.Count
Sheets.Add _
After:=Sheets(Sheets.Count), _
Type:= _
"C:\Documents and Settings\Admin\Application
Data\Microsoft\Templates\New Sheet.XLT"
Sheets("sheet1").Name = NewSheet(n)
Count = 1
Sheets(nLast + 1).Select
Next i
Add_Numbers
Next n

End Sub
Any thoughts on where to go from here are indeed welcomed.

Regards
Peter

Thanks Jim,
Worked perfectly =)

Regards
Peter


"Jim Cone" wrote:

Peter,

Here is my version of your code.
The "Type" designation is commented out so the
code would work on my machine.
Same for the Call to Add_Numbers...

'=============================
Sub Newsheets()
Dim n As Long
Dim NewSheet As Variant

NewSheet = _
Array("31-60", "61-90", "91-120", "121-150")

For n = LBound(NewSheet) To UBound(NewSheet)
If WorksheetExists(NewSheet(n)) = False Then
Exit For
End If
Next 'n
If n UBound(NewSheet) Then
MsgBox "No more sheets to add. "
Exit Sub
Else
With Worksheets.Add(After:=Worksheets(Worksheets.Count) , _
Count:=1, Type:=xlWorksheet)

' "C:\Documents and Settings\Admin\Application Data\" & _
' "Microsoft\Templates\New Sheet.XLT"

.Name = NewSheet(n)
End With
End If
'Add_NumbersFunction
End Sub

'-------------------------------------------
' Function by Chip Pearson 04/20/2003
' Check if Worksheet name exists
' 1st argument passed ByVal to allow use of variants - (Jim Cone)

Function WorksheetExists(ByVal WSName As String, Optional WB As Workbook = Nothing) As Boolean
On Error Resume Next
WorksheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook, WB).Worksheets(WSName).Name))
End Function
'======================================

Regards,
Jim Cone
San Francisco, USA



"Peter" wrote in message
...
Hello All..

I am trying to get this thing to function..without much success.
What it needs to do is..
Only insert one new sheet on request (called from another macro/userform)
Check if that sheet already exists, then insert the next one from the array
where the sheet names are stored.
Here's the code so far..(It just spits out all the sheets in the array in
one go)

Sub Newsheets()

Dim nLast As Long
Dim i As Long
Dim NewSheet
Dim n As Long
Dim a As Long
NewSheet = Array("31-60", "61-90", "91-120", "121-150")

For n = LBound(NewSheet) To UBound(NewSheet)
nLast = Sheets.Count
For i = 1 To ActiveWindow.SelectedSheets.Count
Sheets.Add _
After:=Sheets(Sheets.Count), _
Type:= _
"C:\Documents and Settings\Admin\Application
Data\Microsoft\Templates\New Sheet.XLT"
Sheets("sheet1").Name = NewSheet(n)
Count = 1
Sheets(nLast + 1).Select
Next i
Add_Numbers
Next n

End Sub
Any thoughts on where to go from here are indeed welcomed.

Regards
Peter