In the attached sample file, i am tracking absenteeism per day. Shifts are split into dayshift and night shift. I want to run a macro to check each sheet for the month and populate a seperate MasterData workbook to do a trend analysis.
the MasterData file has a sheet named MasterData with columns A:N being populated. the code that is doing this is as below:
Code: Select all
Option Explicit
Sub ProcessAttendanceData()
Dim masterWorkbook As Workbook
Dim masterSheet As Worksheet
Dim dailyWorkbook As Workbook
Dim dailySheet As Worksheet
Dim folderPath As String
Dim fileName As String
Dim outputRow As Long
Dim dailyDate As Date
Dim shiftType As String
Dim shiftOD As String
Dim totalOnDuty As Double
Dim totalSupervisors As Double
Dim totalSeniors As Double
Dim totalOperators As Double
Dim sickCount As Double
Dim adhocLeaveCount As Double
Dim annualLeaveCount As Double
Dim frlCount As Double
Dim courseCount As Double
Dim overtimeCount As Double
Dim rng As Range
Dim rng2 As Range
Dim cell As Range
' Folder path containing daily workbooks
folderPath = "C:\Users\rfourie1\Documents\Fire\Absenteeism\Control\" ' path to daily workbook
' Open MasterData workbook
Set masterWorkbook = Workbooks.Open("C:\Users\rfourie1\Documents\Fire\Absenteeism\Control\MD_Base_Control.xlsx") ' path to where master data is stored
Set masterSheet = masterWorkbook.Sheets("MasterData")
' Find the next empty row in the MasterData Sheet
outputRow = masterSheet.Cells(masterSheet.Rows.Count, 1).End(xlUp).Row + 1
' Loop through all Excel files in the folder
fileName = Dir(folderPath & "*.xlsm")
Do While fileName <> ""
' Open the daily workbook
Set dailyWorkbook = Workbooks.Open(folderPath & fileName)
' Loop through each sheet in the daily workbook
For Each dailySheet In dailyWorkbook.Sheets
' Get the date from the sheet name
If dailySheet.Name <> "Ranges" And dailySheet.Name <> "Summary" And dailySheet.Name <> "Roster" And dailySheet.Name <> "Template" And dailySheet.Name <> "First" And dailySheet.Name <> "Availability" And dailySheet.Name <> "Last" Then
' dailyDate = DateValue(dailySheet.Name)
dailyDate = DateValue(CleanSheetName(dailySheet.Name))
' Read required data from the daily sheet
shiftType = dailySheet.Range("A4").Value
shiftOD = dailySheet.Range("F5").Value
totalOnDuty = dailySheet.Range("N5").Value
totalSupervisors = dailySheet.Range("J6").Text
totalSeniors = dailySheet.Range("L6").Value
totalOperators = dailySheet.Range("N6").Value
' Count occurrences in E9:E22
Set rng = dailySheet.Range("E9:E22")
sickCount = 0
adhocLeaveCount = 0
annualLeaveCount = 0
frlCount = 0
courseCount = 0
For Each cell In rng
Select Case LCase(cell.Value)
Case "Sick"
sickCount = sickCount + 1
Case "Adhoc Leave"
adhocLeaveCount = adhocLeaveCount + 1
Case "Annual Leave"
annualLeaveCount = annualLeaveCount + 1
Case "FR Leave"
frlCount = frlCount + 1
Case "Course"
courseCount = courseCount + 1
End Select
Next cell
' Count occurrences in F9:F22
Set rng2 = dailySheet.Range("F9:F22")
overtimeCount = 0
For Each cell In rng2
If cell.Value <> "" Then
overtimeCount = overtimeCount + 1
End If
Next cell
' Write data to the MasterData Sheet
With masterSheet
.Cells(outputRow, 1).Value = dailyDate
.Cells(outputRow, 2).Value = Format(dailyDate, "dddd") ' Day name
.Cells(outputRow, 3).Value = shiftType
.Cells(outputRow, 4).Value = "shiftOD" 'shift on duty
.Cells(outputRow, 5).Value = "overtimeCount" ' Overtime
.Cells(outputRow, 6).Value = sickCount
.Cells(outputRow, 7).Value = courseCount
.Cells(outputRow, 8).Value = annualLeaveCount
.Cells(outputRow, 9).Value = adhocLeaveCount
.Cells(outputRow, 10).Value = frlCount
.Cells(outputRow, 11).Value = totalOnDuty
.Cells(outputRow, 12).Value = totalSupervisors
.Cells(outputRow, 13).Value = totalSeniors
.Cells(outputRow, 14).Value = totalOperators
End With
' Move to the next row in MasterData Sheet
outputRow = outputRow + 1
End If
Next dailySheet
' Close the daily workbook
dailyWorkbook.Close SaveChanges:=False
' Get the next file
fileName = Dir
Loop
' Save and close the MasterData workbook
masterWorkbook.Close SaveChanges:=True
MsgBox "Data processing completed!", vbInformation
End Sub
Code: Select all
shiftOD = dailySheet.Range("F5").Value