October 15, 2021

Automate Copying of Master Worksheet

How to automate copying of master worksheet quickly and easily using VBA.

Create Copies of Master Worksheet Automatically

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

Add Month Sheets Automatically in Excel

Further Reading:

Add Month Sheets Automatically in Excel