How to automate copying of master worksheet quickly and easily using VBA.
A small business has a practical problem. They need copies of the master worksheet every month for three work shifts. Doing this manually can be time consuming and boring. However, the process of creating copies of the master worksheet for multiple shifts can be quickly and easily achieved using VBA or Visual Basic for Applications. The complete macro code for the automation of the process is give below:
Sub CopySheetsEveryMonth()
Dim iMonthNumber As Integer
Dim strMonthName As String
Dim iNumDays As Integer
Dim iDay As Integer
Dim iShiftNumber As Integer
Dim wkshtMaster As Worksheet
Dim lCurrentYear As Long
Application.ScreenUpdating = False
iMonthNumber = Application.InputBox(“Enter month number between 1 – 12”, “Enter Month Number 1 to 12”)
If iMonthNumber = False Then Exit Sub
lCurrentYear = Year(Now())
iNumDays = Day(DateSerial(lCurrentYear, iMonthNumber + 1, 0))
Set wkshtMaster = Worksheets(“Master”)
For iDay = 1 To iNumDays
For iShiftNumber = 1 To 3
strMonthName = monthname(iMonthNumber) & ” ” & iDay & ” Shift ” & iShiftNumber
wkshtMaster.Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = strMonthName
Next iShiftNumber
Next iDay
Application.ScreenUpdating = True
End Sub
Further Reading: