Copy "x" number of worksheets

K

Kirk P.

Instead of using Edit => Move or Copy Sheet for each worksheet I want to
copy, is there an alternative method that will allow me to specify the
worksheet I want to copy, and the number of copies I want to make?
 
D

Dave Peterson

You could use a little macro:

Option Explicit
Sub testme()

Dim myCell As Range
Dim HowMany As Long
Dim iCtr As Long

Set myCell = Nothing
On Error Resume Next
Set myCell = Application.InputBox _
(Prompt:="select a cell on the sheet to copy", Type:=8)
On Error GoTo 0
If myCell Is Nothing Then
Exit Sub 'user cancelled
End If

HowMany = Application.InputBox(Prompt:="How many Copies", Type:=1)
If HowMany < 1 Then
Exit Sub
End If

With myCell.Parent 'worksheet
For iCtr = 1 To HowMany
.Copy _
after:=.Parent.Worksheets(.Parent.Worksheets.Count)
Next iCtr
End With

End Sub
 
Top