Worksheets Add & Naming
 

Hi,

I want to duplicate the formulas of a workbook. I want to start with a
different workbook and starting with Sheet2, I want to add an additional 64
sheets. My worksheet name doesn't appear to work.

I am not sure how to properly explain this next part. The internal sheet
numbers might go 1,5,6,7,8...68. Sheets 2-4 were deleted earlier, so when
we go through the loop, XL seems to add internal sheets from where it left
off. Thus, I am forced to deal with Sheet names.

Any assistance with my subroutine you can provide is most appreciated.

Sub DuplicateWorkBook()
Dim iCounter As Integer

For iCounter = 2 To 65
On Error Resume Next
Worksheets("Sheet" & iCounter).Select
If Err.Number < 0 Then
Worksheets.Add.Move after:=Worksheets.Count - 1
'\This next line doesn't seem to work...nothing gets named
Worksheet.Name = "Sheet" & iCounter
Err.Clear
End If
On Error GoTo 0
Next iCounter



'\ I will move this stuff inside the loop once the loop appears to work
correctly
'\ Windows("Book1.xls").Worksheets("Sheet" & iCounter).Cells.Copy
'\ Windows("NewBook.xls").Worksheets("Sheet" & iCounter).Cells.Select
'\ Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
'\ SkipBlanks:=False, Transpose:=False
'\ Range("A1").Select

End Sub


Best regards,
Kevin

Worksheets Add & Naming
 

Try the following: run "Main"
The objective
- make sure that all sheets named Sheet1..Sheet65 exist.
If not , add a sheet with the correct name
Once the missing sheets are added, reorder the workbook


Option Explicit
Public Sub Main()
CreateSheets 2, 65
OrderSheets 2, 65
End Sub
Sub CreateSheets(lMin As Long, lMax As Long)
Dim WS As Worksheet
Dim index As Long
For index = lMin To lMax
Set WS = GetSheet("Sheet" & index)
If WS Is Nothing Then
With Worksheets.Add
.Name = "Sheet" & index
End With
End If
Next
End Sub
Public Sub OrderSheets(lMin As Long, lMax As Long)
Dim WS As Worksheet
Dim index As Long
For index = lMin To lMax
Set WS = GetSheet("Sheet" & index)
If Not WS Is Nothing Then
WS.Move after:=Worksheets(index - 1)
End If
Next
End Sub
Private Function GetSheet(SheetName As String) As Worksheet
On Error Resume Next
Set GetSheet = Worksheets(SheetName)
On Error GoTo 0
End Function



Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Hi,

I want to duplicate the formulas of a workbook. I want
to start with a
different workbook and starting with Sheet2, I want to
add an additional 64
sheets. My worksheet name doesn't appear to work.

I am not sure how to properly explain this next part.
The internal sheet
numbers might go 1,5,6,7,8...68. Sheets 2-4 were deleted
earlier, so when
we go through the loop, XL seems to add internal sheets
from where it left
off. Thus, I am forced to deal with Sheet names.

Any assistance with my subroutine you can provide is most
appreciated.

Sub DuplicateWorkBook()
Dim iCounter As Integer

For iCounter = 2 To 65
On Error Resume Next
Worksheets("Sheet" & iCounter).Select
If Err.Number < 0 Then
Worksheets.Add.Move after:=Worksheets.Count -
1
'\This next line doesn't seem to
work...nothing gets named
Worksheet.Name = "Sheet" & iCounter
Err.Clear
End If
On Error GoTo 0
Next iCounter



'\ I will move this stuff inside the loop once the loop
appears to work
correctly
'\ Windows("Book1.xls").Worksheets("Sheet" &
iCounter).Cells.Copy
'\ Windows("NewBook.xls").Worksheets("Sheet" &
iCounter).Cells.Select
'\ Selection.PasteSpecial Paste:=xlPasteFormulas,
Operation:=xlNone, _
'\ SkipBlanks:=False, Transpose:=False
'\ Range("A1").Select

End Sub


Best regards,
Kevin


.

Worksheets Add & Naming
 

Hi,

Off the cuff, Worksheets( "Sheet" & iCounter).Activate is required to make
it the active worksheet.

Regards,
Don

"Kevin H. Stecyk" wrote in message
...
Hi,

I want to duplicate the formulas of a workbook. I want to start with a
different workbook and starting with Sheet2, I want to add an additional
64
sheets. My worksheet name doesn't appear to work.

I am not sure how to properly explain this next part. The internal sheet
numbers might go 1,5,6,7,8...68. Sheets 2-4 were deleted earlier, so when
we go through the loop, XL seems to add internal sheets from where it left
off. Thus, I am forced to deal with Sheet names.

Any assistance with my subroutine you can provide is most appreciated.

Sub DuplicateWorkBook()
Dim iCounter As Integer

For iCounter = 2 To 65
On Error Resume Next
Worksheets("Sheet" & iCounter).Select
If Err.Number < 0 Then
Worksheets.Add.Move after:=Worksheets.Count - 1
'\This next line doesn't seem to work...nothing gets named
Worksheet.Name = "Sheet" & iCounter
Err.Clear
End If
On Error GoTo 0
Next iCounter



'\ I will move this stuff inside the loop once the loop appears to work
correctly
'\ Windows("Book1.xls").Worksheets("Sheet" & iCounter).Cells.Copy
'\ Windows("NewBook.xls").Worksheets("Sheet" & iCounter).Cells.Select
'\ Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
'\ SkipBlanks:=False, Transpose:=False
'\ Range("A1").Select

End Sub


Best regards,
Kevin

Worksheets Add & Naming
 

Hi Don,

You were correct. I needed to activate the sheet first.

I am going to use Patrick's solution. It is an elegant solution and one
that I will keep for future reference.

Thank you very much for responding.

Best regards,
Kevin

Don Lloyd wrote...
Hi,

Off the cuff, Worksheets( "Sheet" & iCounter).Activate is required to make
it the active worksheet.

Regards,
Don

Worksheets Add & Naming
 

Hi Patrick,

Thank you very much for taking the time and effort to respond with complete
solution. I like your code and will keep it for future reference.

Again, thank you!

Best regards,
Kevin


Patrick Molloy wrote...
Try the following: run "Main"
The objective
- make sure that all sheets named Sheet1..Sheet65 exist.
If not , add a sheet with the correct name
Once the missing sheets are added, reorder the workbook


Option Explicit
Public Sub Main()
CreateSheets 2, 65
OrderSheets 2, 65
End Sub
Sub CreateSheets(lMin As Long, lMax As Long)
Dim WS As Worksheet
Dim index As Long
For index = lMin To lMax
Set WS = GetSheet("Sheet" & index)
If WS Is Nothing Then
With Worksheets.Add
.Name = "Sheet" & index
End With
End If
Next
End Sub
Public Sub OrderSheets(lMin As Long, lMax As Long)
Dim WS As Worksheet
Dim index As Long
For index = lMin To lMax
Set WS = GetSheet("Sheet" & index)
If Not WS Is Nothing Then
WS.Move after:=Worksheets(index - 1)
End If
Next
End Sub
Private Function GetSheet(SheetName As String) As Worksheet
On Error Resume Next
Set GetSheet = Worksheets(SheetName)
On Error GoTo 0
End Function



Patrick Molloy
Microsoft Excel MVP