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
Further reference: