KjBox OzMVP  Super Moderator
 Male
 from Kuching, Borneo
 Member since Jan 10th 2007
 Last Activity:
 Forum HIRE HELP
 Posts
 4,385
 Likes Received
 40
 Points
 22,346
 Profile Hits
 1,776

Hi there,
I would like to ask for a code for my problem which is i have 3 sheets, let say A,B and C. Sheet B is taking its values from sheet A, i would like a code that when i run it, to deactivate sheet A and activate Sheet C in such Sheet B will take its value from Sheet C not B.
Please advice and thanks in advance

Hi There,
Anyone can please help me on selecting the Titus Classification(Comm Addin) using a VBA, and when i tried using the sendkeys it didnt work.
when the macro is running to select any option in the classification using send keys, the options are acyually invisible in the Command bar.
Am struggling from 1 week just scratching my head to find a resolution for this one.
Please help me out, Thanks in Advance...!!!

Hi KJBox, hope you had a good new year.
You provided me with the below codes that worked great...but since we changed to 2020 I get nothing counted at all. is there a problem with the date in the code?
Code Sub GetCountsCurrentMonth()
 Dim x, y, Rws, ContAreas, i As Long, ii As Long
 Const Category As String = "Work"
 Const Status As String = "Processed"
 Const DtCol As Long = 11 '// Change to suit (11 = column K)
 Rws = Array(5, 6, 7, 8, 9, 17, 18, 19, 20, 21, 22, 23, 24, 32, 33, 34, 35, 36)
 ContAreas = Array("WSCA1", "WSCA2", "SECA11", "SECA12", "SECA13", "NWCA5", "NWCA6A", _
 "NWCA6B", "NWCA7", "NWCA8", "NWCA9", "NWCA10A", "NWCA10B", _
 "WSCA3", "WSCA4", "SECA14", "SECA15", "SECA16")
 x = Sheet1.[a6].CurrentRegion
 ReDim y(1 To 36, 1 To 1)
 For i = 2 To UBound(x, 1)
 If Year(x(i, DtCol)) = Year(Date) And Month(x(i, DtCol)) = Month(Date) _
 And x(i, 4) = Category And x(i, 13) = Status Then
 For ii = LBound(ContAreas) To UBound(ContAreas)
 If ContAreas(ii) = x(i, 12) Then y(Rws(ii), 1) = y(Rws(ii), 1) + 1
 Next
 End If
 Next
 With Sheet2
 .[e1].Resize(36) = y
 .Activate
 End With
 End Sub
Code Sub GetCountsPreviousMonth()
 Dim x, y, Rws, ContAreas, i As Long, ii As Long
 Const Category As String = "Work"
 Const Status As String = "Processed"
 Const DtCol As Long = 11 '// Change to suit (11 = column K)
 Rws = Array(5, 6, 7, 8, 9, 17, 18, 19, 20, 21, 22, 23, 24, 32, 33, 34, 35, 36)
 ContAreas = Array("WSCA1", "WSCA2", "SECA11", "SECA12", "SECA13", "NWCA5", "NWCA6A", _
 "NWCA6B", "NWCA7", "NWCA8", "NWCA9", "NWCA10A", "NWCA10B", _
 "WSCA3", "WSCA4", "SECA14", "SECA15", "SECA16")
 x = Sheet1.[a6].CurrentRegion
 ReDim y(1 To 36, 1 To 1)
 For i = 2 To UBound(x, 1)
 If Year(x(i, DtCol)) = Year(Date) And Month(x(i, DtCol)) = Month(Date)  1 _
 And x(i, 4) = Category And x(i, 13) = Status Then
 For ii = LBound(ContAreas) To UBound(ContAreas)
 If ContAreas(ii) = x(i, 12) Then y(Rws(ii), 1) = y(Rws(ii), 1) + 1
 Next
 End If
 Next
 With Sheet2
 .[e1].Resize(36) = y
 .Activate
 End With
 End Sub
Code Sub GetCountsCurrentQuarter()
 Dim x, y, Rws, ContAreas, i As Long, ii As Long
 Const Category As String = "Work"
 Const Status As String = "Processed"
 Const DtCol As Long = 11 '// Change to suit (11 = column K)
 Rws = Array(5, 6, 7, 8, 9, 17, 18, 19, 20, 21, 22, 23, 24, 32, 33, 34, 35, 36)
 ContAreas = Array("WSCA1", "WSCA2", "SECA11", "SECA12", "SECA13", "NWCA5", "NWCA6A", _
 "NWCA6B", "NWCA7", "NWCA8", "NWCA9", "NWCA10A", "NWCA10B", _
 "WSCA3", "WSCA4", "SECA14", "SECA15", "SECA16")
 x = Sheet1.[a6].CurrentRegion
 ReDim y(1 To 36, 1 To 1)
 For i = 2 To UBound(x, 1)
 If Year(x(i, DtCol)) = Year(Date) And DatePart("q", x(i, DtCol)) = DatePart("q", Date) _
 And x(i, 4) = Category And x(i, 13) = Status Then
 For ii = LBound(ContAreas) To UBound(ContAreas)
 If ContAreas(ii) = x(i, 12) Then y(Rws(ii), 1) = y(Rws(ii), 1) + 1
 Next
 End If
 Next
 With Sheet2
 .[e1].Resize(36) = y
 .Activate
 End With
 End Sub
Code Sub GetCountsPreviousQuarter()
 Dim x, y, Rws, ContAreas, i As Long, ii As Long
 Const Category As String = "Work"
 Const Status As String = "Processed"
 Const DtCol As Long = 11 '// Change to suit (11 = column K)
 Rws = Array(5, 6, 7, 8, 9, 17, 18, 19, 20, 21, 22, 23, 24, 32, 33, 34, 35, 36)
 ContAreas = Array("WSCA1", "WSCA2", "SECA11", "SECA12", "SECA13", "NWCA5", "NWCA6A", _
 "NWCA6B", "NWCA7", "NWCA8", "NWCA9", "NWCA10A", "NWCA10B", _
 "WSCA3", "WSCA4", "SECA14", "SECA15", "SECA16")
 x = Sheet1.[a6].CurrentRegion
 ReDim y(1 To 36, 1 To 1)
 For i = 2 To UBound(x, 1)
 If Year(x(i, DtCol)) = Year(Date) And DatePart("q", x(i, DtCol)) = DatePart("q", Date)  1 _
 And x(i, 4) = Category And x(i, 13) = Status Then
 For ii = LBound(ContAreas) To UBound(ContAreas)
 If ContAreas(ii) = x(i, 12) Then y(Rws(ii), 1) = y(Rws(ii), 1) + 1
 Next
 End If
 Next
 With Sheet2
 .[e1].Resize(36) = y
 .Activate
 End With
 End Sub
Potholes
Hi Charles I hope you are keeping well, and safe from covid19. !!!
Next Tuesday I am getting surgery on both of my eyes to correct some vision problems, Soooooo I am staying home from any of the possible germ bags (Carriers) out there.
Funny how the Mayan calendar predicted a pandemic in 2020.
Keep well everybody
Peter ( Potholes)