K
Kevin H. Stecyk
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
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