Welcome to the second part of our series. In this section, we will be covering the basic and intermediate VBA tools that are frequently used for data consolidation in Excel.
In the office, report and data consolidation is a routine occurrence that often requires a lot of time doing mundane copying and pasting of data. Thankfully, VBA allows us to automate this task, freeing up our time for higher order processes.
It is important to note however, that in order for us to use VBA to automate these tasks, they must follow a repetitive sequence of events, and the data inputs & outputs must have the same format each time. If data needs to be extracted from a different source in each iteration of the report or exercise, or if the output requires a different format, then VBA cannot be used to automate these tasks.
Areas covered in this tutorial:
- Importing and Exporting Data Between Workbooks
- Last Row/Column
- Searching Within Cells
- Cell Formatting
1. Importing and Exporting Data Between Worksheets
Firstly, we will be looking at moving data across Excel Sheets. To do this, we will be building upon our knowledge of variables that we covered in the first part of our tutorial series. Here we will be introducing the variables “worksheets” and “workbooks”.
Worksheets are variables that allow us to store Excel Sheets as variables to be called upon later, and can help us save a lot of memory space in code writing, especially when we have to refer to the same sheet multiple times.
The format for definining worksheets is as follows:
Dim WS1 As Worksheet, WS2 As Worksheet Set WS1 = Sheets("First Sheet") Set WS2 = Sheets("Second Sheet")
We will now look at an example of how data can be imported using the worksheets variable:
In the code above, we defined 2 worksheets, WS1 and WS2, referring to the “Module 1” and “Log” sheets respectively. Then we use the code “WS2.Range(“A2:12”).Copy to copy the cells from A2 to A12 on the Sheet “Log”. Afterwards, we activate the “Module 1” sheet to paste our selected values.
In doing so, we have saved memory space my minimizing the amount of code that needs to be written, and this is especially so when the sheets need to be referenced multiple times.
The “Range” function here is VBA code that allows us to quickly select a range of cells with the format:
Range("Beginning Cell:Ending Cell")
2. Importing and Exporting Data Between Workbooks
A more common use of VBA is the importing and exporting of data between Workbooks, and this is frequently used in report and/or data consolidation. The format for defining Workbooks is as follows:
Dim WB1 As Workbook, WB2 As Workbook Set WB1 = ThisWorkbook Set WB2 = Workbooks.Open("File path of folder containing workbook & its name")
We will now look at an example of how data can be exported using the worksheets variable:
As we can see here, we have defined 2 Workbooks, our currently active Workbook and the Workbook that we wished to export our data to. Depending on where you have stored your own Workbooks, your file path will change. We then copy our selected cells and paste them into our target workbook, called “Test”, giving us the following results:
An important point to note is that at the very end of your file path, after the Workbook name is the file extension. Depending on your file type, this will change, and very often if your code cannot open the desired file, it is because the file type doesn’t match. So be very sure that you have specified the correct extension, be it xlsm, xls or xlsx.
3. Last Row/Column
Previously, we covered the use of fixed range loops to activate VBA code on a range of cells. However, in many cases we would be dealing with a dynamic range, with the last set of data being placed on a different row or column each time. To deal with this, we use variable range loops, and the Last Row/Column function is 1 manner in which we can create a variable range loop.
Dim lastRow as integer lastRow = Cells(Rows.Count, ColmNo).End(xlUp).Row Dim lastColm as Integer lastColm = Cells(RowNo,Columns.Count).End(xlToLeft).Column
In our example above, we take the last row number by initiating a “Ctrl+Shift+Up” from the lowest point in our specified column, and we take the last row number by initiating a “Ctrl+Shift+Up” from the rightmost point in our specified row.
With these numbers, we can then create a variable range loop to cycle through multiple rows or columns, executing VBA code in each iteration such as with the example below:
Here, we cycle through our If statement through cells B3 to the last specified row in column A, which is B13. This gives us the following output:
4. Cell Formatting
In certain cases, especially with report creation, we need to perform formatting on our Excel outputs, and sometimes this can be very cumbersome with extremely large datasets.
This can be easily automated with the use of VBA and the record macro function. Using this, we can record the formats that we are assigning to our cells, and we can then use VBA to automatically apply those formats in the future.
Say we wish to have a cell have the color red and have a certain custom format. We can simply record our macro and then apply the recorded macro in the future using loops and cell references.
PivotTables are some of the most popular ways in which business professionals oversee operations, and can be used to assess a wide variety of data ranging from a breakdown of sales by region to expenses by product line. As with our previous section on formats, it is far easier to record the creation of a PivotTable than to create one from scratch, providing us with something like this:
This is especially useful when we are creating templates, and the data is always pulled from the exact same range of cells, allowing us to create a new PivotTable each time. However, as we can clearly see, the creation of a PivotTable and specifying which fields are under Columns, Rows, Filters and Values requires an incredible amount of VBA code.
A much simpler alternative, is to simply create a PivotTable, specify the range where it retrieves its data along with some leeway in the form of excess cell ranges, and then using the code below to simply refresh the PivotTable:
As we can clearly see, this is a much shorter piece of code, and is by far more efficient in minimizing the size of your VBA files. It works by refreshing the PivotTable by referencing the current data in the specified range of the PivotTable. The only thing to note is the name assigned to the PivotTable, which can be seen in the top left corner in the picture here:
These features are explored in greater detail in the working file, which you can use to gain a greater understanding of the topics covered in this section. In the next part of our series, we will be looking at advanced conditional statements, filtering and search functions!
This tutorial was put together by Goh Jun Hui.