Copy a set of sheets and rename them

[Jmoz092]

I'm trying to copy a set of sheets that are paired by formulas to each other, a time sheet of sorts, and a corresponding invoice sheet. I can copy them no problem, but I'm having trouble figuring out code to:
1) make a copy of one master and place it after the master sheets then make a copy of the 2nd master and place it after the master sheets AND after the new copy(s) of the first master sheet
2) repeating this as many times as user input requires.

Right Now I'm running each of the code below (thanks Fluff) and then I have to manually drag the Invoice sheet to its matching time sheet.

So we'll wind up with sheets: Data, masterTime, MasterInvoice, copy1MasterTime, copy1MasterInvoice, copy2MasterTime, copy2MasterInvoice, etc,etc

Is there any way to automate this with code?

[COLOR=#454545][FONT='inherit']Sub AddSheetsT()[/FONT][/COLOR]

[COLOR=#454545][FONT='inherit']    Dim Cnt As Long[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']    Dim sTemp As String[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']    Dim dSDate As Date[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']    Dim Shts As Long[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']
[/FONT][/COLOR]

[COLOR=#454545][FONT='inherit']    sTemp = InputBox("First Monday for the first worksheet:", "MM-DD-YY")[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']    dSDate = CDate(sTemp)[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']    Shts = (54 - WorksheetFunction.WeekNum(dSDate))[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']Application.ScreenUpdating = False[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']   For Cnt = 1 To Shts[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']      Sheets("MasterTime”).Copy after:=Sheets(Sheets.Count)[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']      ActiveSheet.Name = Format(dSDate, "mm-dd-yy") & " Time"[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']        dSDate = dSDate + 7[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']    Next Cnt[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']End Sub[/FONT][/COLOR]

[COLOR=#454545][FONT='inherit']Sub AddSheetsI()[/FONT][/COLOR]

[COLOR=#454545][FONT='inherit']    Dim Cnt As Long[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']    Dim sTemp As String[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']    Dim dSDate As Date[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']    Dim Shts As Long[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']
[/FONT][/COLOR]

[COLOR=#454545][FONT='inherit']    sTemp = InputBox("First Monday for the first worksheet:", "MM-DD-YY")[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']    dSDate = CDate(sTemp)[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']    Shts = (54 - WorksheetFunction.WeekNum(dSDate))[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']Application.ScreenUpdating = False[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']   For Cnt = 1 To Shts[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']      Sheets("MasterInvoice”).Copy after:=Sheets(Sheets.Count)[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']      ActiveSheet.Name = Format(dSDate, "mm-dd-yy") & " Invoice"[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']        dSDate = dSDate + 7[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']    Next Cnt[/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']End Sub[/FONT][/COLOR]

 

[Norie]

Why not copy both sheets at the same time?

Sheets(Array("MasterTime", "MasterInvoice")).Copy After:=Sheets(Sheets.Count)

Sheets(Sheets.Count-1).Name = Format(dSDate, "mm-dd-yy") & " Time"
Sheets(Sheets.Count).Name = Format(dSDate, "mm-dd-yy") & " Invoice"

 

[Jmoz092]

/facepalm

Thank you norie!

 

[Jmoz092]

So this work, Norie, but there is a named range that is used on the MasterTime sheet that populates a validated drop down list and when the copies are being made, there's a pop up message :

A formula or sheet you want to move or copy contains the name 'AAA', which already exists on the destination worksheet.
Do you want to use this version of the name?
- To use the name as defined in the destination sheet, click yes
-To rename the range referred to in the formula or worksheet, Click No, and enter a new name in the Name Conflict dialog box


Can I bypass this by using

applicationupdate = false

?

I do need to actually click Yes though. Is there a way to automatically do that and yet not have the pop up appear before each pair of sheets is created?

 

[Norie]

To bypass that alert you would use this,

Application.DisplayAlerts = False

but I'm not sure if that would be the same as clicking 'Yes'.

 

[Jmoz092]

To bypass that alert you would use this,

Application.DisplayAlerts = False

but I'm not sure if that would be the same as clicking 'Yes'.

that worked, it kept the validated list on the new copies. Thank Norie