How to Customise Excel Reports Using Macros, Visual Basic, and Select All Sheets

Satyvir Jasra, 09 October 2017

Recently for one of my projects, I was required to create an Audit Report which showed all the mandatory fields within the client’s Microsoft Dynamics 365 environment. Upon doing some research, I figured out that “Metadata Document Generator” tool from http://www.xrmtoolbox.com/ can be used to generate such a report.

As shown in the screenshot above, the report includes lots of information which a user may not want to see. With the help of Microsoft Excel’s “Macros” and “Select All Sheet” functionality, I needed to filter and customise the report as required. In this blog, I will walk through the steps to simplify the report. Visual Basic will be used to modify Macros.

Select All Sheets

Right click on any sheet and select the “Select All Sheet” option (steps highlighted in yellow). Once all sheets are highlighted, select whole column/row that you may want to remove. In this case I wanted to remove all columns except A, B, and G. To do so, while holding “Control” key on your keyboard, click on top of the column, in this case I selected C, D, E, F,I, and J. Once all columns have been selected, select “Delete” option. This will remove the selected columns from all sheets. Steps are highlighted in Red.

Be sure to “Ungroup All Sheets”, this can be achieved in same way where you selected all sheets. Missing this step will cause issues when running Macros.

Using Macros and Visual Basic to Filter All Sheets

Using “Select All” we have the option to delete different rows and columns from all sheets, but we cannot filter all sheets without the help of Macros and Visual Basic code. Go back to first sheet of the document, click on “View”, then select “Macros” and then “Record Macros”. (Steps highlighted in Blue). Enter the Macro name, and then click on OK.

Once the Macro starts recording, select “Stop Recording”, this can be done from the same option list where you selected “Record Macro”. In that same option list, you will select “View Macro”, then select “FilterAllSheets” Macro and click on “Edit”. A window will open with some Visual Basic code.

For each sheet, I wanted the table to be filtered based on “Required Level”. After filtering only those rows should be displayed where Requirement level is either  “Application Required” or “System Required”. Based on the requirements I replaced the code as shown in the screenshot below and clicked on “Run” icon.

As seen in the screenshot below, I selected a different sheet and the table is filtered as desired. Also by looking at the row numbers, we can tell that none of the data was deleted from the table, only filtered. The filter can be removed at any time.