I'm soliciting this community's help for suggestions on making my code better. My current code does function properly, but I'd like to make it more efficient and not so large. The code listed below has been edited to only include a sampling of the case statement. The total code has 20 cases and is repeated for multiple worksheets.
The code's purpose is to compile metrics for a source of data (worksheet "Raw_Data") and post those metrics in a template (worksheet "Cover Page(Asia)").
The code's purpose is to compile metrics for a source of data (worksheet "Raw_Data") and post those metrics in a template (worksheet "Cover Page(Asia)").
Code:
Sub Populate_OrderTables()
Dim b, c, i, j, k, l, m, n, o, p, q As Integer
Dim ordstatCol, ordtypeCol, subbuCol As Range
Dim dataWS_RD As Worksheet
Dim rowLimit As Integer
Dim colLimit As Integer
Dim xlapp As Excel.Application
Dim xlWb As Workbook
Dim xlWs As Worksheet
Set dataWS_RD = ThisWorkbook.Sheets("Raw_Data")
Worksheets("Raw_Data").Activate
rowLimit = dataWS_RD.UsedRange.Rows.Count
colLimit = dataWS_RD.UsedRange.Columns.Count
'Find Order Status column
For i = 1 To colLimit
If Left(dataWS_RD.Cells(1, i).Value, 12) = "Order Status" Then
Set ordstatCol = Range(Cells(1, i), Cells(rowLimit, i))
Exit For
End If
Next i
'Find Order Type column
For p = 1 To colLimit
If Left(dataWS_RD.Cells(1, p).Value, 10) = "Order Type" Then
Set ordtypeCol = Range(Cells(1, p), Cells(rowLimit, p))
Exit For
End If
Next p
'Find Sub Business Unit column
For q = 1 To colLimit
If Left(dataWS_RD.Cells(1, q).Value, 17) = "Sub Business Unit" Then
Set subbuCol = Range(Cells(1, q), Cells(rowLimit, q))
Exit For
End If
Next q
Worksheets("Cover Page(Asia)").Activate
For Each y In ActiveSheet.Range("b4", "b7:b33").Cells
Select Case y
Case "Level 1"
y.Offset(0, 1).Value = WorksheetFunction.Sum _
(WorksheetFunction.CountIfs(subbuCol, "Asia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 1"), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "eCommerce" & char(10) & "China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 1")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Bangladesh" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 1")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Cambodia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 1")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 1")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - India" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 1")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Pakistan" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 1")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Thailand" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 1")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Vietnam" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 1")))
Case "Level 2"
y.Offset(0, 1).Value = WorksheetFunction.Sum _
(WorksheetFunction.CountIfs(subbuCol, "Asia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 2"), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "eCommerce" & char(10) & "China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 2")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Bangladesh" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 2")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Cambodia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 2")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 2")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - India" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 2")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Pakistan" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 2")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Thailand" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 2")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Vietnam" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 2")))
Case "Level 3"
y.Offset(0, 1).Value = WorksheetFunction.Sum _
(WorksheetFunction.CountIfs(subbuCol, "Asia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 3"), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "eCommerce" & char(10) & "China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 3")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Bangladesh" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 3")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Cambodia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 3")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 3")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - India" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 3")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Pakistan" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 3")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Thailand" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 3")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Vietnam" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 3")))
Case "Level 4"
y.Offset(0, 1).Value = WorksheetFunction.Sum _
(WorksheetFunction.CountIfs(subbuCol, "Asia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 4"), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "eCommerce" & char(10) & "China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 4")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Bangladesh" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 4")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Cambodia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 4")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 4")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - India" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 4")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Pakistan" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 4")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Thailand" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 4")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Vietnam" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 4")))
Case "Level 5"
y.Offset(0, 1).Value = WorksheetFunction.Sum _
(WorksheetFunction.CountIfs(subbuCol, "Asia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 5"), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "eCommerce" & char(10) & "China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 5")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Bangladesh" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 5")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Cambodia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 5")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 5")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - India" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 5")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Pakistan" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 5")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Thailand" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 5")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Vietnam" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 5")))
Case "Level 6"
y.Offset(0, 1).Value = WorksheetFunction.Sum _
(WorksheetFunction.CountIfs(subbuCol, "Asia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 6"), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "eCommerce" & char(10) & "China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 6")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Bangladesh" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 6")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Cambodia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 6")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 6")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - India" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 6")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Pakistan" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 6")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Thailand" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 6")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Vietnam" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 6")))
Case "Level 7"
y.Offset(0, 1).Value = WorksheetFunction.Sum _
(WorksheetFunction.CountIfs(subbuCol, "Asia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 7"), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "eCommerce" & char(10) & "China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 7")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Bangladesh" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 7")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Cambodia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 7")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 7")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - India" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 7")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Pakistan" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 7")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Thailand" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 7")), _
(WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Vietnam" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 7")))
End Select
End Sub