Summary Sheet in Excel using VBA Macro
For the last few weeks, it has been release mode at my work so there has not been a lot of personal time for my own fun projects. One of the reasons there has been so little activity here lately. But, the release went out a little over a week ago, and things are (somewhat) back to normal. I say somewhat because the QA department I work for has went from 2 of us to 6. Good news, but now comes all the work necessary to get this department into a shape that can support more people. This means building out test suites...Lots of them. So, for the past week we have all been meeting to discuss and design our hierarchy of test suites, establishing our Traceability Matrix, Acceptance Test outline and creating our Test Scenario templates. With all of that done, we began tackling the actual writing of the test scenarios. Some documents were in existence that we have been able to leverage, to a degree. While others simply are nowhere to be found. Our new lead needs a way to easily report on what is in each document (currently an Excel spreadsheet). The first step was to generate the test scenarios contained on each worksheet of our document, then create a front page or summary page that listed out each worksheet name with those counts, along with the total test count. This is what I came up with below.
'Auto_Open allows the macro to run every time the workbook is opened. This will allow for the test counts to remain up to date.
Dim myRange As Range
Dim k As Integer, i As Integer, testTotal As Integer, sheetTotal As Integer
Dim sheetName As String
'Get the number of Worksheets in the Workbook
k = Worksheets.Count
For i = 2 To k
'Activate the next Worksheet
Application.Sheets(i).Activate
'Counts cells in B Column that have data
Set Rng = ActiveSheet.Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp)).SpecialCells(xlCellTypeVisible)
'Get worksheet total cells with data, remove 3 to account for headings. they dont count.
sheetTotal = Application.WorksheetFunction.CountA(Rng) - 3
'total up all test cases found.
testTotal = testTotal + sheetTotal
'Get current worksheet name
sheetName = ActiveSheet.name
'Reset active sheet to "Summary" for updating
Application.Sheets("Summary").Activate
'Reset active cell to A1
Application.Cells(1, 1).Activate
'Put sheet name into row A, cell i
Application.ActiveCell(i, 1) = sheetName
'Put the sheet total into row B, cell i
Application.ActiveCell(i, 2) = sheetTotal
'Give us some room so the tab titles do not extend outside of the cell
Columns("A:A").ColumnWidth = 45
Next i
'Update the sheet with the totals for all test cases
Application.Sheets("Summary").Activate
Application.Cells(2, 1).Activate
Application.ActiveCell(1, 2) = testTotal
End Sub
Next, I will be creating a macro to update our traceability matrix with these numbers automatically. After that, most likely will be creating a macro to calculate the total test cases that have been completed, how many passed versus failed and so forth. Lots of fun!
See the updated macro here including the downloadable files.
