Understanding a VBA macro to make changes to it

[nickbelle9]

In my job, we use a VBA macro in an excel sheet to create a new workbook with the template sheet duplicated for the amount of workdays in the current month. I want to change it so that it can be repeated for any month, and unfortuntely the person that created this macro no longer works here. Looking at the code, I'm having trouble understanding which part of it determines which month to use, even though there are comment related to it.

In the template workbook where this code is taken from there are 2 sheets, named "Template" and "Data". It duplicates the "Template" sheet but still will not run without the "Data" sheet being there.

Can someone please let me know which part I should change to make it duplicate for a different month than the current one?

Option Explicit



'------------------------------------------------
Sub DaySheet()
' Macro to create sheets from Template for each _
  working weekday of this month
'------------------------------------------------


    If Workbooks.Count > 1 Then
    MsgBox "Error: Too many Workbooks are open" & vbCr & _
    "Close all but the Template and run this macro again."
    Exit Sub
    End If


    Dim iD As Integer, iM As Integer, iMax As Integer
    Dim wbWB As Workbook, wbT As Workbook
    Dim s As String
    Dim WS As Worksheet
        
    
    iM = Month(Date)
    iD = 1
    iMax = dhDaysInMonth(DateValue(Year(Date) & "/" & iM & "/01"))
    
    Set wbT = ThisWorkbook
    wbT.Sheets("Template").Copy
    Set wbWB = ActiveWorkbook
    wbT.Sheets("Data").Copy after:=Sheets("Template")

    Do
        If isWorkDay(DateValue(Year(Date) & "/" & iM & "/" & iD)) Then
            ' create sheet
            wbWB.Sheets("Template").Copy after:=Sheets(wbWB.Sheets.Count)
            ActiveSheet.Name = Format(Date, "MMM-") & iD
        
            Range("A2:A100").Select
            Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=IF(ISERROR(MATCH($A2,Data!$A$2:$A$100,0)),"""",MATCH($A2,Data!$A$2:$A$100,0))"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
        End With
                    Selection.FormatConditions(1).StopIfTrue = False
        End If
        
            iD = iD + 1
    Loop While iD <= iMax
    Sheets("Template").Visible = 0
    Sheets("Data").Visible = 0
' Colour_Correct Macro
' Change the colour of newly created workbook
'

'
    ActiveWorkbook.Theme.ThemeColorScheme.Load ( _
        "C:\Program Files (x86)\Microsoft Office\Root\Document Themes 16\Theme Colors\Office 2007 - 2010.xml" _
        )
    
    Worksheets(1).Activate
End Sub

'--------------------------------------------------------------
Function dhDaysInMonth(Optional dtmDate As Date = 0) As Integer
' Function that returns the number of days in the month _
  passed as argument. If no argument then the number of _
  days in current month
'--------------------------------------------------------------

    ' Return the number of days in the specified month.
    If dtmDate = 0 Then
        ' Did the caller pass in a date? If not, use
        ' the current date.
        dtmDate = Date
    End If
    dhDaysInMonth = DateSerial(Year(dtmDate), _
     Month(dtmDate) + 1, 1) - _
     DateSerial(Year(dtmDate), Month(dtmDate), 1)
End Function


'--------------------------------------------------------
Function isWorkDay(dDate As Date) As Boolean
' Function that returns TRUE if date passed as argment _
  is a work day. You can set the weekend days below in _
  the function.
'--------------------------------------------------------
    Dim iWE1 As Integer, iWE2 As Integer, iWD As Integer
    
    ' Define weekend days. 1=Monday, 7=Sunday
    iWE1 = 6
    iWE2 = 7
    
    ' get the day number (1=Monday, 7=Sunday)
    iWD = Application.WorksheetFunction.Weekday(dDate, 2)
    ' check if is weekend day
    If iWD <> iWE1 And iWD <> iWE2 Then
        isWorkDay = True
    Else
        isWorkDay = False
    End If

End Function

 

[johnnyL]

To change the month, Find the following line of code:

   iM = Month(Date)

Right now, that line of code equates to 7 for July

Change that line to the number of the month that you want, for example if you change it to:

   iM = 8

It should do the 8th month ie. the month of August

iM = 9 would do September. Etc.

 

[nickbelle9]

To change the month, Find the following line of code:

   iM = Month(Date)

Right now, that line of code equates to 7 for July

Change that line to the number of the month that you want, for example if you change it to:

   iM = 8

It should do the 8th month ie. the month of August

iM = 9 would do September. Etc.

That seems to have worked! All the days line up as the work days for August when I change it to 8. However, the sheets are named for each day, and the name of the sheets still show July. How would I change that as well.

Thanks for your help!

 

[johnnyL]

Find the following line:

            ActiveSheet.Name = Format(Date, "MMM-") & iD

and change it to:

            ActiveSheet.Name = MonthName(iM) & iD

 

[nickbelle9]

Find the following line:

            ActiveSheet.Name = Format(Date, "MMM-") & iD

and change it to:

            ActiveSheet.Name = MonthName(iM) & iD

That also worked, thanks again!

 

[johnnyL]

Glad to help.