Sub Create_Attendance_Sheet_Through_VBA()

'Defined variable for the sheet of "Create_Attendance_Sheet"
Dim SH As Worksheet
Set SH = ThisWorkbook.Sheets("Create_Attendance_Sheet")

'Defined the Year
Dim YearNumb As Integer
YearNumb = SH.Range("D4").Value

' If starting month is greater than ending month exit for
If SH.Range("E4").Value > SH.Range("H4").Value Then
MsgBox ("Starting month should be greater than ending month")
Exit Sub
End If

'Number of Sheet in Newly Created Workbook
Application.SheetsInNewWorkbook = 1

'Created Object variable for newly created workbook
Dim XLWkb As Workbook
Set XLWkb = Workbooks.Add ' Added new workbook

'Find the last data row number based on column "A"
LastDatarow = SH.Range("A" & Rows.Count).End(xlUp).Row

Dim MonthNumb As Integer
Dim r As Integer
SheetNumber = 1
'Defined varibale for newly creating worksheet
Dim NewSH As Worksheet
For MonthNumb = SH.Range("E4").Value To SH.Range("H4").Value
If SheetNumber = 1 Then
Set NewSH = XLWkb.ActiveSheet
Else
Set NewSH = XLWkb.Worksheets.Add(after:=Sheets(XLWkb.Sheets.Count))
End If
ActiveWindow.DisplayGridlines = False
'Providing the sheet name
NewSH.Name = MonthName(MonthNumb) & " " & YearNumb
'MsgBox (Day(DateSerial(YearNumb, MonthNumb + 1, 0)))
For d = 1 To Day(DateSerial(YearNumb, MonthNumb + 1, 0))
'adding data
NewSH.Cells(1, d + 1).Value = DateSerial(YearNumb, MonthNumb, d)
'adding day
NewSH.Cells(2, d + 1).Value = WeekdayName(Weekday(NewSH.Cells(1, d + 1)))
Next

'Adding Date and Day
NewSH.Cells(1, 1).Value = "Date"
NewSH.Cells(2, 1).Value = "Day"

'Find the Last data row and copy student name into newly created workbook
'Datarow = SH.Range("A" & Rows.Count).End(xlUp).Row
SH.Range("A2:A" & LastDatarow).Copy NewSH.Range("A3")
Application.CutCopyMode = xlCopy

'Define the Last used column number and row number in newly created worksheet
LastColumn = NewSH.Range("A1").End(xlToRight).Column
Lastrow = NewSH.Range("A1").End(xlDown).Row

'Adding header after last column
NewSH.Cells(1, LastColumn + 1).Value = "Total Present"
NewSH.Cells(1, LastColumn + 2).Value = "Total Absent"

'Adding Dates,Days, Dropdown for "P", "A"
For r = 1 To Lastrow ' Loop all the rows
For C = 1 To LastColumn 'Loop all the columns for each row
NewSH.Cells(r, C).Activate
With NewSH.Cells(r, C)
If r >= 3 And C >= 2 Then
' P will display to those cells other than saturday and sunday
If NewSH.Cells(2, C).Value <> "Sunday" And NewSH.Cells(2, C).Value <> "Saturday" Then
.Value = "P"
End If
'Adding data validation
.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="P,A"
.Validation.IgnoreBlank = True
.Validation.InCellDropdown = True
End If
'Changing the Horizontal alignment
If r > 2 And C = 1 Then
.HorizontalAlignment = xlLeft
Else
.HorizontalAlignment = xlCenter
End If
'Providing Interior color index, font color index
If r = 1 Then
.Interior.ColorIndex = 9
.Font.ColorIndex = 2
.Font.Bold = True

ElseIf r = 2 Then
.Interior.ColorIndex = 56
.Font.ColorIndex = 6
.Font.Bold = True


ElseIf r > 2 And NewSH.Cells(2, C).Value = "Sunday" _
Or NewSH.Cells(2, C).Value = "Saturday" Then
.Interior.ColorIndex = 15
Else
.Interior.ColorIndex = 20
.Font.ColorIndex = 1
End If
End With

Next 'End of Iteration for Column Loop
'Adding count if formulas from 3rd row onwards
If r >= 3 Then

'MsgBox ("=COUNTIF(B" & r & ":" & Cells(r, Lastcolumn).Address(False, False) & ", ""P"")")
NewSH.Cells(r, LastColumn + 1).Formula = "=COUNTIF(B" & r & ":" & Cells(r, LastColumn).Address(False, False) & ", ""P"")"
NewSH.Cells(r, LastColumn + 2).Formula = "=COUNTIF(B" & r & ":" & Cells(r, LastColumn).Address(False, False) & ", ""A"")"
'Providing formatting to the last formula cells
With NewSH.Range(Cells(r, LastColumn + 1), Cells(r, LastColumn + 2))
.Interior.ColorIndex = 10
.Font.ColorIndex = 2
.HorizontalAlignment = xlCenter
End With
End If
Next ' Rows Loop - End of external Loop
'==============================================
'Provide the formatting to header rows of Count formula
With NewSH.Range(Cells(1, LastColumn + 1), Cells(2, LastColumn + 2))
.Interior.ColorIndex = 10
.Font.ColorIndex = 2
.HorizontalAlignment = xlCenter
End With

NewSH.UsedRange.Font.Size = 15
NewSH.UsedRange.Font.Name = "Century"
NewSH.UsedRange.Columns.AutoFit
SheetNumber = SheetNumber + 1
Next ' Months Loop

Application.SheetsInNewWorkbook = 3

MsgBox ("Hi Pavan Completed")

End Sub