This code works fine to create worksheets but it cannot be ran twice in a row. What I would like to have it do is ask how many copies are needed and be able do this when requested and go with one number higher than the last worksheet, which is always on the right. Can somebody see what is stopping it from doing that? If the existing worksheets are Report 156 then Report 157, the next two would be Report 158 and Report 159 etc.
Code:
Sub Copy_Sheets()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
If ActiveSheet.Name <> "Report" Then
MsgBox "You must be in ''Report'' to run this macro."
Exit Sub
End If
Sheets("Report").Select
Dim x As Integer
Dim i As Integer
Dim CurSheet As String
CurSheet = ActiveSheet.Name
i = Application.InputBox("How many sheets are needed?", "Add Sheets", Type:=1)
Do While x < i
ActiveSheet.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Report " & x + 1
x = x + 1
Sheets(CurSheet).Activate
Loop
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub