Create new workbook with copied sheet 'x' amount of times

[redspanna]

Hi all

Can anybody give some code that will do the following

1> copy sheet2 from active workbook
2> open new workbook
3> add the copied Sheet2 to the new workbook multiplied by the number shown in cell G2 of sheet "Display" in the original workbook.
4> Then rename each new sheet 1,2,3,4,5, etc NOT SHEET1, SHEET2 etc

EDIT:

I just found this code online to rename the sheets 1,2,3,4,5 etc

Sub renameSheets()
Dim iSheetCount

    For iSheetCount = 1 To Sheets.Count
        Sheets(iSheetCount).Name = iSheetCount
    Next iSheetCount

End Sub

So for example if G2 holds the number 15, the 'Display' sheet will be copied 15 times into the new workbook and they will then be renamed 1-15

So if anybody could adapt the above code to carry out steps 1-3 as above first I think that might work

many thanks

 

[redspanna]

Think I've got it....

Sub copy()
    Set NewBook = Workbooks.add
    ThisWorkbook.Sheets("Template").copy Before:=NewBook.Sheets(1)
    Sheets("Template").Select
    Set baseSheet = ActiveSheet
    howmany = Range("A1")
    For i = 2 To howmany
    baseSheet.copy after:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = Format(Date + i, "dd")
    Next i
    
    Application.DisplayAlerts = False
    Sheets("Sheet1").Delete
    Application.DisplayAlerts = True
    
    Dim iSheetCount
    For iSheetCount = 1 To Sheets.Count
    Sheets(iSheetCount).Name = iSheetCount
    Next iSheetCount



    End Sub

note: I have changed some sheet names but this seems to do the trick