Uploaded January 2020 | Updated September 2026, 1 week ago

---

How to create multiple copies of master sheet automatically based on entry of number of month.
Here's the complete VBA code:
Option Explicit

Sub createSheetsForShiftsInMonth()
Dim CurYear As Long
Dim intMonth As Integer
Dim intDay As Integer
Dim intShift As Integer
Dim intNumDaysInMonth As Integer
Dim shtMaster As Worksheet
Dim strTemp As String

'get the number of the desired month as 1 to 12

intMonth = InputBox("enter the number of month. For example, 1 for January, 2 for February, etc.")
CurYear = Year(Now())
intNumDaysInMonth = Day(DateSerial(CurYear, intMonth + 1, 0))
'MsgBox intNumDaysInMonth

Set shtMaster = Worksheets("Master")

For intDay = 1 To intNumDaysInMonth
For intShift = 1 To 3
strTemp = MonthName(intMonth) & " " & intDay & " Shift " & intShift
shtMaster.Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = strTemp
Next intShift
Next intDay

End Sub

More details available at exceltrainingvideos.com/create-multiple-copies-of-master-sheet-automatically