Hi,
This is my current macro to auto populate a summary table from Sheet1 to a new worksheet named "Summary". The table consists of Reported, Unreported and Total parts.
Now I need help to modify the macro so that it can also insert a new column in Sheet1 (column K) named "To Do". The values in this column should referenced to the Reported and Unreported criteria.
I have attached the excel file to show how Sheet1 looks like.
TIA!
Code
Sub TiaPersonal()
Dim wb As Workbook, sh As Worksheet
Dim Arr, ws As Worksheet
Dim Dt1 As String, Dt2 As String, Diff As String
Dim i As Long, lr As Long, Val1 As Long, Val2 As Long
Application.ScreenUpdating = False
Dt1 = Format(Date, "yyyy-mm-dd")
Set wb = ActiveWorkbook
Set sh = wb.Sheets(1)
With sh
lr = .Cells(.Rows.Count, 1).End(xlUp).Row
Arr = .Range("A10:G" & lr).Value
For i = LBound(Arr, 1) To UBound(Arr, 1)
If Not Arr(i, 7) = "" Then
Dt2 = Arr(i, 7) & "-01"
Diff = DateDiff("m", Dt2, Dt1)
If Arr(i, 5) = "N" Then
If Diff <= 24 Then
Val1 = Val1 + 1
ElseIf Diff > 24 Then
Val2 = Val2 + 1
End If
ElseIf Arr(i, 5) = "Y" Then
If Diff <= 12 Then
Val1 = Val1 + 1
ElseIf Diff > 12 Then
Val2 = Val2 + 1
End If
End If
ElseIf Arr(i, 7) = "" And Arr(i, 5) = "" Then
Val2 = Val2 + 1
End If
Next i
End With
Set ws = Sheets.Add(After:=Sheets(Sheets.Count)): ws.Name = "Summary"
With ws
.Range("A1").Resize(3) = Application.Transpose(Array("Reported", "Unreported", "Total"))
.Range("B1").Resize(3) = Application.Transpose(Array(Val1, Val2, Val1 + Val2))
End With
Application.ScreenUpdating = True
End Sub
Display More