Copy Worksheets

[Papi]

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.

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

 

[Colo]

Hi Papi,

I think that u need to get the latest # from the exiting workshees first.

Sub Copy_Sheets_colo()
    Const sCurshName As String = "Report"
    Dim lLastNum As Long
    Dim lWksNeed As Long
    Dim i As Long
    
    Application.ScreenUpdating = False
    
    'Get latest number
    lLastNum = Trim(Mid(Sheets(Sheets.Count).Name, Len(sCurshName) + 1))
    lngWksNeed = Application.InputBox("How many sheets are needed?", "Add Sheets", Type:=1)
    
    'Canceled
    If lngWksNeed = False Then Exit Sub
    
    For i = 1 To lngWksNeed
        Sheets(sCurshName).Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = sCurshName & lLastNum + 1
        lLastNum = lLastNum + 1
    Next
    
    Application.ScreenUpdating = True
End Sub

 

[Papi]

Thank you very much Colo.