Sheets("Timetable").Select 'Select the right sheet to copy rangeRange("E4").Select 'Select the right cell to copy rangeSelection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _    :=False, Transpose:=True 'Paste Special to the cell'To clear previous dataRange("H4:L34").ClearContents'To key in OT dataDim Day 'Stores the day of the weekDim PH 'Stores whether a particular day is a public holidayDim OT_Hours As Double 'Stores the OT Hours on a weekdayDim Total_Hrs As Double 'Stores the total number of hours worked in a particular dayDim day_count As Integer 'Sets the loop from the row of day 1 to row of day 31Dim Rmk 'Stores if LL/OL/CSE/etcRange("E4").Select 'Select the right cell to start for keying and calculation of OT hours, note its in "Template" sheetFor day_count = 1 To 31 'Loop for one month, maximum is 31 days    Day = ActiveCell.Value 'Current cell gives the value of the day of the week, to be stored as day    ActiveCell.Offset(0, 1).Select 'Move 1 cell to the right    PH = ActiveCell.Value 'Current cell states whether its a public holiday, to be stored as PH    ActiveCell.Offset(0, 1).Select 'Move 1 cell to the right    Total_Hrs = CDec(ActiveCell.Value) 'Current cell gives the total number of hours worked that day, to be stored as Total    'All cases that results in the different OT hours        If Total_Hrs = 0 Then    ElseIf Day = "SA" And PH = "Y" Then 'If Saturday and Public Holiday        ActiveCell.Offset(0, 4).Select 'Move 4 cells to the right        ActiveCell.Value = Total_Hrs 'Assign the active cell OT hours (X3)    ElseIf Day = "SA" Then 'If Saturday and not Public Holiday        ActiveCell.Offset(0, 2).Select 'Move 2 cells to the right        ActiveCell.Value = Total_Hrs 'Assign the active cell OT hours (X1.5)    ElseIf Day = "SU" And PH = "Y" Then 'If Sunday and Public Holiday        ActiveCell.Offset(0, 5).Select 'Move 5 cells to the right        ActiveCell.Value = Total_Hrs 'Assign the active cell OT hours (X4)    ElseIf Day = "SU" Then 'If Sunday and not Public Holiday        ActiveCell.Offset(0, 3).Select 'Move 3 cells to the right        ActiveCell.Value = Total_Hrs 'Assign the active cell OT hours (X2)    ElseIf PH = "Y" Then 'If Public Holiday on weekday        If Total_Hrs > 8 Then 'If there is OT on Public Holiday            OT_Hours = Total_Hrs - 8 'Calculate the effective OT hours            ActiveCell.Offset(0, 1).Select 'Move 1 cell to the right            ActiveCell.Value = 8  'Assign the active cell OT hours (X1)            ActiveCell.Offset(0, 1).Select 'Move 1 cell to the right            ActiveCell.Value = OT_Hours 'Assign the active cell OT hours (X1.5)        Else            ActiveCell.Offset(0, 1).Select 'If no OT on Public holiday, just move one cell right and key in            ActiveCell.Value = Total_Hrs 'Assign active cell OT hours (X1)        End If    ElseIf PH = "HD" Then        If Total_Hrs > 4 Then 'If there is OT on a half-day working day            OT_Hours = Total_Hrs - 4 'Calculate the effective OT hours            ActiveCell.Offset(0, 2).Select 'Move 1 cell to the right            ActiveCell.Value = OT_Hours 'Assign the active cell OT hours (X1.5)        Else        End If    ElseIf Total_Hrs > 8 Then 'If Weekday and OT        OT_Hours = Total_Hrs - 8 'Calculate the effective OT hours        ActiveCell.Offset(0, 2).Select 'Move 2 cells to the right        ActiveCell.Value = OT_Hours 'Assign the active cell OT hours (X1.5)    End If    OT_Hours = 0 'Restart OT Hours    Selection.End(xlToRight).Select 'Move selected cell all the way to the right    ActiveCell.Offset(1, -8).Select 'Move selected cell to next row and column to startNext day_count'Append the monthly data from each staff to the Raw Data Sheet'Declare all required variables to store dataDim Name 'Name of personnelDim Month 'Month of workDim SCode 'Staff CodeDim NormHr As Double 'Normal Hours, applicable for part-timeDim OTHr As Double 'OT Hours, applicable for full-timeDim Other As DoubleDim DOR As Date 'Date of reportDim DoneBy 'HR Personnel that prepared the spreadsheetDim Leave As Double 'Days of leaveDim MC As Integer 'Days of MCDim TrainingHr As Double 'TrainingHrs in that monthDim Days As Integer 'Days in that month'Store all data as variablesMonth = Range("C2").Value 'Store monthSCode = Range("G2") 'Store staff codeNormHr = CDec(Range("G36").Value) 'Store normal hours of work, for part-timersOTHr = CDec(Range("G37").Value) 'Store OT hours (effective, e.g. if X1.5, OTHr = 1.5 and not 1)Other = CDec(Range("G38").Value) 'Store other reburisements/deductionsDOR = Range("G41").Value 'Store date of reportDoneBy = Range("G42").Value 'Store personnel who key in dataLeave = CDec(Application.WorksheetFunction.CountIf(Range("C4:C34"), "LL")) 'Store total local leaveLeave = Leave + Application.WorksheetFunction.CountIf(Range("C4:C34"), "OL") 'Store total local leave + overseas valueLeave = Leave - 0.5 * Range("G39").Value 'Account for half-day leaveMC = Application.WorksheetFunction.CountIf(Range("C4:C34"), "MC") 'Store total MC days for that monthTrainingHr = CDec(Range("G40").Value) 'Store total training hours for that monthDays = 31 - WorksheetFunction.CountA("F4:F34") 'Store total no. of days in that monthSheets("StaffData").Select 'Move to sheet "StaffData"'Select Cell B2 to start'Declare all required variables to store data'Start with variables from spreadsheetDim SCodeSD 'Staff Code from the StaffData SheetDim Nat 'NationalityDim Status 'Whether personnel is full/part-timeDim Basic As Double 'Basic Pay (For full-time)Dim DayRate As Double 'Day Rate (For part-time)Dim HrRate As Double 'Hourly Rate (For both full and part-time)Dim Age As Double 'Age, will affect CPF rateDim PRYears As Double 'No. of years of PR, will affect CPF rateDim LeaveE As Double 'Leave Entitled'Other intermediate variables requiredDim Total As Double 'Total amount from basic and over-timeDim LeaveL As Double 'Leave LeftDim MCT As Integer 'MC TotalDim TrainingT As Double 'Training TotalDim CPF_ee As Double 'CPF EmployeeDim CPF_er As Double 'CPF EmployerDim CDAC As Double 'CDAC ContributionsActiveSheet.ListObjects("StaffInfo").Range.AutoFilter Field:=2, _    Criteria1:=SCode 'Filter by staff code'Creating dictionary, looping through and store data is not a good solution because it takes up process time'expotentially as data increases and increases interim memory space'Better method is to do filter as aboveRange("StaffInfo[[#Headers],[Staff Code]]").Select  'Select Cell B2 (column of staff code to start)Selection.End(xlDown).Select 'Select the last row (actually there is only one row of available name after filter)Name = ActiveCell.Offset(0, -1).Value 'Store nameActiveCell.Offset(0, 2).Select 'Move three columns rightNat = ActiveCell.Value 'Stores whether or not he/she is SingaporeanActiveCell.Offset(0, 5).Select 'Move five columns rightStatus = ActiveCell.Value 'Stores whether he/she is full/part-timeActiveCell.Offset(0, 1).Select 'Move one column rightBasic = CDec(ActiveCell.Value) 'Stores monthly pay of employeeActiveCell.Offset(0, 1).Select 'Move one column rightDayRate = CDec(ActiveCell.Value) 'Stores day rate of employeeActiveCell.Offset(0, 1).Select 'Move one column rightHrRate = CDec(ActiveCell.Value) 'Stores hour rate of employeeActiveCell.Offset(0, 1).Select 'Move one column rightAge = CDec(ActiveCell.Value) 'Stores age of employeeActiveCell.Offset(0, 1).Select 'Move one column rightIf ActiveCell.Value = "NA" Then    PRYears = NAElse    PRYears = CDec(ActiveCell.Value) 'Stores no. of years of PREnd IfActiveCell.Offset(0, 2).Select 'Move two colums rightLeaveE = ActiveCell.Value 'Stores no. of days of leave entitledIf Status = "F" Then    Total = CDec(Basic + OTHr * HrRate)ElseIf Status = "P" Then    Total = CDec(NormHr * HrRate + OTHr * HrRate)End If'Don't use nested If. Use dictionary keypad values for CPF or create another sheet'(like what I have done)'Consider customising cases if possible'Nationality keypad valuesIf Nat = "PR" And PRYears > 3 Then    Nat = "S"ElseIf Nat = "PR" And PRYears > 2 Then    Nat = "PR2"ElseIf Nat = "PR" And PRYears > 1 Then    Nat = "PR1"ElseIf Nat = "S" Then    Nat = "S"Else    Nat = "N"End IfSheets("CPF").Select 'Move sheet to CPF to key in data to calculate CPFCDAC = CDec(Range("L1").Value) 'Assign CDAC ammount from cellRange("C1") = Total 'Assign cell with total salaryRange("F1") = Nat 'Assign cell with NationalityRange("I1") = Age 'Assign cell with ageRange("H3").Select 'Select the start row of CPF employeeSelection.End(xlDown).Select 'Shift cell all the way downCPF_ee = CDec(ActiveCell.Value) 'Assign CPF of employee from cell valueActiveCell.Offset(0, 1).Select 'Shift cell one column to rightCPF_er = CDec(ActiveCell.Value) 'Assign CPF of employer from cell valueSheets("PaymentData").Select 'Move sheet to RawData to key in stuffRange("B2").Select 'Select the right cell to startSelection.End(xlDown).Select 'Move cursor to end row that is filledActiveCell.Offset(1, 0).Select 'Move one row downActiveCell.Value = Name 'Assign name to cellActiveCell.Offset(0, 1).Select 'Move one column rightActiveCell.Value = SCode 'Assign staff code to cellActiveCell.Offset(0, 1).Select 'Move one column rightActiveCell.Value = Month 'Assign month to cellActiveCell.Offset(0, 1).Select 'Move one column rightIf Basic = 0 Then    Basic = Total - OTHr * HrRateElseEnd IfActiveCell.Value = Basic 'Assign Basic Pay amount to cellActiveCell.Offset(0, 1).Select 'Move one column rightActiveCell.Value = OTHr * HrRate 'Assign OT Pay amount to cellActiveCell.Offset(0, 1).Select 'Move one column rightActiveCell.Value = CPF_ee 'Assign CPF Employee amount to cellActiveCell.Offset(0, 1).Select 'Move one column rightActiveCell.Value = CPF_er 'Assign CPF Employer amount to cellActiveCell.Offset(0, 1).Select 'Move one column rightActiveCell.Value = OtherActiveCell.Offset(0, 1).Select 'Move one column rightActiveCell.Value = Leave 'Assign no. of days of leave on that month to cellActiveCell.Offset(0, 1).Select 'Move one column rightActiveCell.Value = MC 'Assign total no. of MC daysActiveCell.Offset(0, 1).Select 'Move one column rightActiveCell.Value = TrainingHr 'Assign total no. of training hours on that month to cell'Creating dictionary, looping through and store data is not a good solution because it takes up process time'expotentially as data increases and increases interim memory space'Copy data from Template and StaffData, verify and count if requiredActiveSheet.ListObjects("MonthlyData").Range.AutoFilter Field:=2, Criteria1 _    :=SCode 'Filter by Staff CodeRange("J2").Select 'Column that stores leave that monthLeaveL = CDec(LeaveE - WorksheetFunction.SumIf(Range("C:C"), SCode, Range("J:J")))'Assign leave left by deducting entitled - sums up all cells in that columnRange("K2").Select 'Column that stores MC that monthMCT = CDec(WorksheetFunction.SumIf(Range("C:C"), SCode, Range("K:K"))) 'Total no. of MCsRange("L2").Select 'Column that stores training hours that monthTrainingT = CDec(WorksheetFunction.SumIf(Range("C:C"), SCode, Range("L:L"))) 'Total no. of training hoursSelection.End(xlDown).Select 'Move cursor to end row that is filledActiveCell.Offset(0, 1).Select 'Move one column rightActiveCell.Value = LeaveL 'Assign no. of days of leave left to cellActiveCell.Offset(0, 1).Select 'Move one column rightActiveCell.Value = MCT 'Assign cumulative no. of MC to cellActiveCell.Offset(0, 1).Select 'Move one column rightActiveCell.Value = TrainingT 'Assign total training hours to cellActiveCell.Offset(0, 1).Select 'Move one column rightActiveCell.Value = DoneBy 'Assign HR Personnel that prepared the spreadsheetActiveCell.Offset(0, 1).Select 'Move one column rightActiveCell.Value = CDec(Total - CPF_ee - CDAC + Other) 'Assign Nett PayActiveCell.Offset(0, -14).Select 'Move 14 columns to left and end