Excel VBA - Generate a report in dashboard format

Application with Case Study
For the example in previous articles, I bet you guys think it is not much challenging. Let's develop more complicated application!

I am going to assume that you are competent to take the advantage of worksheet functions in Excel. Imagine that your boss requested you to submit a monthly report for customer analysis. Nevertheless, you now are busy enough but cannot refuse and even lose the job. Come on~poor guy! To prevent from this happen again, I strongly recommend to build a report template for next monthly reports. In this case, a lists of transaction records (Transaction.csv) and members data (Members.xlsx) in your hand. Of course, our target is to generate a complete report with raw data as soon as possible.

Process Flow:

Preparation
First of all, to open a empty workbook and save as macro-enabled workbook (.xlsm) which allows to save the macros. Otherwise, you could not lose all macros after closing the workbook as excel workbook (.xlsx) does not retains them.

Remember to save as Excel Macro-Enabled Workbook!

Download the source files containing raw data through the dropbox and find:
1. Members.xlsx: Members profile and information
2. Transactions.csv: Transaction data in June

Create a template
Design the template for the reports prior to inserting the module. Suppose there are three worksheets named "Raw_data", "Member_profile" and "Report". In the sheet of "Raw_data", list out all columns of raw data and then the calculated column (in light green color) in the beginning. Input the formula with vlookup function under the calculated column such that we can auto-fill for each transaction records.

Tips: The below formula is used to look-up and get the values of member name, membership type, gender and age of user.
e.g. Member_name

=IF(ISERROR(VLOOKUP(B2,Member_profile!$A$2:$G$11,2,FALSE))=FALSE,VLOOKUP(B2,Member_profile!$A$2:$G$11,2,FALSE),"")

Actually, the end-user of "Report" sheet is your boss and therefore, it is necessary to design a clear layout and well-formatting in the report. You could ignore this statement if you really hate your boss. Cells with light green color consists of formula with countif and sumif function. Having pasted the transaction data in "Raw_data" sheet, these cells will automatically calculate and show the corresponding values. Of course, this is just an example demonstrating the process flow of reporting. You could design own template by the applying the excel formula and inserting the charts for visualising the data.

Empty report

 
Record Macro
As mentioned in introduction, macro can be developed by recording the work procedures in excel instead of tedious coding. After getting a grip on replacement of variable to hard-code, you could develop the most of Sub applications instead of writing the code.
The template is successfully created in the previous steps such that we only need to put the data into the sheets of "Raw_data" and "Member profile" and activate "Report" sheet.

1. Click "Record Macro"

2. Copy the members' information(exclude column name) in Member.xlsx and paste to the sheet of "Member_profile".

3. Copy the transaction records(exclude column name) in Transaction.csv and paste to the sheet of "Raw_data" as well.

4. Auto fill the formula from row 2 to row 31

5. Close the workbooks of both Transaction.csv and Member.xlsx

6. Activate the sheet of "Report"

7. Stop "Record Macro"

8. Open the VBE and find the module1

Sub recorded_update()
    Windows("Members.xlsx").Activate
    Range("A2:F11").Select
    Selection.Copy
    
    Windows("Reporting Template.xlsm").Activate
    Sheets("Member_profile").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Windows("Transactions.csv").Activate
    Range("A2:F31").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Windows("Reporting Template.xlsm").Activate
    Sheets("Raw_data").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Range("G2:L2").Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("G2:L31"), Type:=xlFillDefault
    Range("G2:L31").Select
    
    
    Windows("Members.xlsx").Activate
    ActiveWindow.Close
    Windows("Transactions.csv").Activate
    ActiveWindow.Close
    
    Sheets("Report").Select
    ActiveWorkbook.Save
End Sub

Tips: To find the macro (record_update.bas) recorded in my repository of GitHub. 

Customize the macro with VBA
The code is going to be modified so as to fit any case to generate a monthly reports. I have highlighted the change of coding in macro (Sub update) and leave some explanations. Please find the update.bas in my repository.

All name worksheet and workbook are set into variables to avoid hard-code to minimize time for code updating once these names needs to be changed.

Sub update()
    'Remarks: The code included in ' ' means outdated and replaced by others
              'Command after ' is my own comment


    Set report_book = ActiveWorkbook  'Set is needed as ActiveWorkbook is a Object
    Set report_console_sheet = ActiveSheet
    Set report_member_sheet = Worksheets("Member_Profile")  'Need update here once changed the worksheet name
    Set report_trans_sheet = Worksheets("Raw_data")
    Set report_report_sheet = Worksheets("Report")
    'Windows("Members.xlsx").Activate'

Insert a code (Application.GetOpenFilename) to get the open filename of Transactions.csv & Members.xlsx so that they are able to be opened in pop-up. member_file will return "False" after you do not choose any file and close the pop-up.

    ChDir ActiveWorkbook.Path  'Set the default location for import file

    'Get the Filename of Member.xlsx
    member_file = Application.GetOpenFilename(fileFilter:="xlsx files (*.xlsx),*.xlsx", Title:="Import the Members.xlsx")
    If member_file = False Then
        Exit Sub
    End If
    Workbooks.Open (member_file)
    Set member_book = ActiveWorkbook  'Avoid hardcode again
    Set member_sheet = ActiveSheet

As numbers of member and transaction records varied with every month, variables (members_num, trans_num) are replaced to the value of 10 and 30. To copy both transaction data and member profile and paste into Workbook of report.

    members_num = member_sheet.Range("A1").End(xlDown).Row - 1  'The address of member_sheet.Range("A1").End(xlDown) is $A$11
    'Range("A2:F11").Select'
    Range("A2:F" & members_num).Select  'As the number of members varies with month
    Selection.Copy
    
    'Windows("Reporting Template.xlsm").Activate'
    report_book.Activate 'Avoid hardcode
    'Sheets("Member_profile").Select'
    report_member_sheet.Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    'Get the Filename of Transactions.csv
    transaction_file = Application.GetOpenFilename(fileFilter:="csv files (*.csv),*.csv", Title:="Import the Transactions.csv")
    If transaction_file = False Then
        Exit Sub
    End If
    Workbooks.Open (transaction_file)
    Set transaction_book = ActiveWorkbook  'Avoid hardcode again
    Set transaction_sheet = ActiveSheet
    'Windows("Transactions.csv").Activate'
    
    trans_num = transaction_sheet.Range("A1").End(xlDown).Row - 1
    
    'Range("A2:F31").Select'
    Range("A2:F" & trans_num).Select  'As the number of transaction varies with month
    Selection.Copy
    
    'Windows("Reporting Template.xlsm").Activate'
    report_book.Activate
    report_trans_sheet.Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Range("G2:L2").Select
    Application.CutCopyMode = False
    'Selection.AutoFill destination:=Range("G2:L31"), Type:=xlFillDefault'
    Selection.AutoFill destination:=Range("G2:L" & trans_num), Type:=xlFillDefault
    'Range("G2:L31").Select'
    
    
    'Windows("Members.xlsx").Activate'
    'ActiveWindow.Close'
    member_book.Close (False)  'The params, False tells excel do not save the workbook
    
    'Windows("Transactions.csv").Activate'
    'ActiveWindow.Close'
    transaction_book.Close (False)
    
    'Sheets("Report").Select'
    report_report_sheet.Select
    
    report_member_sheet.Visible = False  'Hide the worksheets of Raw_data, Members_profile
    report_trans_sheet.Visible = False
    report_console_sheet.Visible = False
    
    'ActiveWorkbook.Save'
    report_book.Save
    
    MsgBox "Success! The report is generated!"
        
End Sub

Optimizing the UX
As the user to generate the report regularly, eliminating unessential procedures as most as possible are mostly preferred. As a consequence, I create a new worksheet named "Console" and insert a button assigned with macro of "update". Users only are required to press the button and choose 2 files and the completed reports are generated. Apart from that, the alert and update in screen will not be displayed. The module with revised coding is as follows:

Sub update()
    'Remarks: The code included in ' ' means outdated and replaced by others
    '         Command after ' is my own comment
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    Set report_book = ActiveWorkbook  'Set is needed as ActiveWorkbook is a Object
    Set report_console_sheet = ActiveSheet
    Set report_member_sheet = Worksheets("Member_Profile")  'Need update here once changed the worksheet name
    Set report_trans_sheet = Worksheets("Raw_data")
    Set report_report_sheet = Worksheets("Report")
    'Windows("Members.xlsx").Activate'
    
    
    ChDir ActiveWorkbook.Path  'Set the default location for import file

    'Get the Filename of Member.xlsx
    member_file = Application.GetOpenFilename(fileFilter:="xlsx files (*.xlsx),*.xlsx", Title:="Import the Members.xlsx")
    If member_file = False Then
        Exit Sub
    End If
    Workbooks.Open (member_file)
    Set member_book = ActiveWorkbook  'Avoid hardcode again
    Set member_sheet = ActiveSheet
    
    members_num = member_sheet.Range("A1").End(xlDown).Row - 1  'The address of member_sheet.Range("A1").End(xlDown) is $A$11
    'Range("A2:F11").Select'
    Range("A2:F" & members_num).Select  'As the number of members varies with month
    Selection.Copy
    
    'Windows("Reporting Template.xlsm").Activate'
    report_book.Activate 'Avoid hardcode
    'Sheets("Member_profile").Select'
    report_member_sheet.Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    'Get the Filename of Transactions.csv
    transaction_file = Application.GetOpenFilename(fileFilter:="csv files (*.csv),*.csv", Title:="Import the Transactions.csv")
    If transaction_file = False Then
        Exit Sub
    End If
    Workbooks.Open (transaction_file)
    Set transaction_book = ActiveWorkbook  'Avoid hardcode again
    Set transaction_sheet = ActiveSheet
    'Windows("Transactions.csv").Activate'
    
    trans_num = transaction_sheet.Range("A1").End(xlDown).Row - 1
    
    'Range("A2:F31").Select'
    Range("A2:F" & trans_num).Select  'As the number of transaction varies with month
    Selection.Copy
    
    'Windows("Reporting Template.xlsm").Activate'
    report_book.Activate
    report_trans_sheet.Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Range("G2:L2").Select
    Application.CutCopyMode = False
    'Selection.AutoFill destination:=Range("G2:L31"), Type:=xlFillDefault'
    Selection.AutoFill destination:=Range("G2:L" & trans_num), Type:=xlFillDefault
    'Range("G2:L31").Select'
    
    
    'Windows("Members.xlsx").Activate'
    'ActiveWindow.Close'
    member_book.Close (False)  'The params, False tells excel do not save the workbook
    
    'Windows("Transactions.csv").Activate'
    'ActiveWindow.Close'
    transaction_book.Close (False)
    
    'Sheets("Report").Select'
    report_report_sheet.Select
    
    report_member_sheet.Visible = False  'Hide the worksheets of Raw_data, Members_profile
    report_trans_sheet.Visible = False
    report_console_sheet.Visible = False
    
    'ActiveWorkbook.Save'
    report_book.Save
    
    MsgBox "Success! The report is generated!"
        
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True  'Turn on the function after running this macro
End Sub

Congratulation! The summary report is shown on the screen!

Shared with you
I admitted it is quite confused for beginner so I have shared excel workbook (Reporting Template.xlsm) with all modules I developed in my repository of GitHub. Hope this can help you guys. Enjoy!!

Should you have any queries, please feel free to leave a comment.