Excel VBA Report Automation

In this video, we will explain how you can automate the creation of reports. We would like to create automatic reports in Excel. This can be done by making use of Excel VBA.

0:00 Excel VBA Report Automation Intro 0:29 Excel VBA – Developer 1:13 Excel VBA Report Automation Code

VBA Code: https://softtechtutorials.com/microsoft-office/excel/excel-vba-report-automation/

You can include the Developer tab in the ribbon by navigating to File – Options and Customize Ribbon or you just move your arrow to the ribbon, right-click and select Customize the Ribbon. On the right side, you can check the box next to Developer and press OK.

Select Developer and next click on Visual Basic. Now, the VBA code opens. We have already prepared the code called createProductReports. Remark that we have written this code into a module. To open a new one, you navigate to Insert and select Module.

At the start of your VBA code, it is always useful to type Option Explicit. This forces you to declare all your variables and prevents you from making mistakes.

Next, we define the output folder for our reports. At the start of our function, we initialize all variables that we use. First, we consider the sheet that contains our data. Then we have a variable that tracks the final row of our data.

Next, we have a variable that tracks the final row for each list of the same products. Using uniqueProductList we consider all unique products in our data and we will use the variable product to loop through the list.

Next, we use the variable book to create new Excel files in which we paste the data per product. Finally, the variable rowNumber is used to go through all rows in the data and extract all unique product names.

Now that all variables are initialized, we start with the creation of the reports. Next, we indicate that we will work with this sheet. We set finalRow equal to the last row that contains data by counting the number of rows in column A.

If we only have one line of data, there is only one product type on the sheet, so we don’t have to create separate reports.

In the next lines, we will create a list of all unique products. We first create a new collection and then loop through all products and add all unique values. We have added “on error resume next” to make sure that we don’t get an error message when a product is identified that is already part of the collection.

In the next part of the code, we filter data on product type and copy-paste the data to a new Excel file. To do that, we loop through the unique product list. We select the entire range of data, filter on each unique product, and copy this data.

Next, we create a new workbook and paste the data in there. When you have pasted this data, it’s also possible to add whatever you li ... https://www.youtube.com/watch?v=8eY5rEcR6AE