Microsoft Office Tutorials and References
In Depth Information
CREATE WORKSHEET NAMES BY USING THE FILL HANDLE
Dim ws As Worksheet ‘ original worksheet, the one to copy
Dim wst As Worksheet ‘ worksheet to hold fi ll handle series
Dim wb As Workbook ‘ temporary workbook to hold fi ll handle
series
Dim wbt As Workbook ‘ activeworkbook
Dim wsn As Worksheet ‘ most recently created sheet
Set ws = ActiveSheet
Set wbt = ActiveWorkbook
Application.ScreenUpdating = False
Part
3
‘ Which index number is the current sheet?
‘ Note that Ctr will be 1 less than the current sheet, so that
in the loop, you can use Ctr+i
Ctr = ws.Index - 1
x = InputBox( _
Prompt:="How many new worksheets to create?", _
Title:="Fill Handle for Worksheets", Default:=11)
‘ Add a temporary workbook with a single worksheet
Set wb = Workbooks.Add(xlWBATWorksheet)
Set wst = wb.Worksheets(1)
‘ Enter current worksheet name in cell A1 of temp worksheet
wst.Cells(1, 1).Formula = "’" & ws.Name
‘ Using xlFillSeries instead of xlFillDefault will allow
‘ a number such as "1" to extend to "1", "2", "3".
wst.Cells(1, 1).AutoFill Destination:=wst.Cells(1, 1).Resize(x
+ 1, 1), Type:=xlFillSeries
For i = 2 To x + 1
ws.Copy after:=wbt.Worksheets(Ctr + i - 1)
Set wsn = ActiveSheet
‘ if duplicate name, don’t bother renaming
On Error Resume Next
wsn.Name = wst.Cells(i, 1).Value
On Error GoTo 0
Next i
‘ Close the temporary workbook
 
Search JabSto ::




Custom Search