Purpose: You may have a report published on the regular basis with lots of formatting process. This session will tell you how to leverage VBA to automate the formatting part to save you lots of time.
Input: I took a product class-level profitability report as an example.
Here is the structure of raw data:
1. Data stored in worksheet “Raw_Data”.
2. 10 columns including Business unit info, Division, Department, Class hierarchy info and sales, COGS and class-level margin information.
3. This is just a sample data. In reality, you should have big enough data (lots of columns or rows) so that it makes sense to build VBA process to save time.
Output: Our goal is to have the table being applied with following formats.
1. Header formatting.
2. Row merge fro BU/ Division/ Department columns.
3. Subtotal row formatting.
4. Font size adjustment.
5. Border applied.
6. Dollar sign applied.
VBA code: I broke the process into several steps so that we can view the change step by step.
1. Header Format: Define the last column number to apply the format from first column to the last one for the first row.
2. Font Size: For all cells, define the font size.
3. Format Others: Apply color to specific column.
4. Subtotal Format: Apply format to the rows with class 9999 (Subtotal).
5. Merge: Merge higher layer of hierarchy to make the table view neat.
6. Border/ Currency: Apply border for whole table and currency data type for the Sales/ COGS/ Margin columns.
THANK YOU! ENJOY IT :)
