By now, you are probably aware of all the good things that you can you with VBA. Everything that is possible by using „simple“ Excel can be also automated with VBA.
One of these things is the so-called „calling“ of other sheets from any sheet of our choosing. Of course, we can refer to the other sheets by clicking on them, but that can often prove to be the harder way, especially when you see the options at your disposal in the example below.
Opening the Module and Referring to Sheets
For our example, we will create five sheets: „Calculations“, „One digit numbers“, „Two-digit numbers“, „Three-digit numbers“, and „Four-digit numbers“:
In the first sheet, i.e. „One digit numbers“ we will input one-digit random numbers (from 1 to 9) in range A1:D1.
In the sheet „Two-digit numbers” we will insert numbers from 10 to 99, and so on.
Now, to open up our VBA module, we will click the ALT + F11 combination on our keyboard. On the window that appears, we will right-click anywhere on the left window, and select Insert >> Module:
On the right side of the module, you will also notice all the sheets that we created in our Workbook:
To see the properties of these sheets, you need to go to View >> Properties Window or simply press F4 while in the module:
Now you can see the properties of every sheet by clicking on it:
As seen, you can also change the name of our sheet here. It is important to know that changing the sheet name in this window will not impact the name that a user sees when looking at the workbook. However, this also means that the name here is not changeable by the user unless she or she knows where to look.
This is important because we can reference the sheet either by this name, by the name that is given in our workbook, or by the ordering number of the sheet (the order in which the sheets were created).
For our exercise, we will refer directly to the sheet names in the Workbook.
Get Values From Another Sheet with VBA
In our first sheet called “Calculation”, we will populate the sum of various cells in multiple sheets, meaning that we will sum values from cell A1 from every sheet.
We will input the following code in our Module:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Sub CallingSheets() Dim one, two, three, four As Long Sheets("One digit numbers").Activate one = Range("A1") Sheets("Two digit numbers").Activate two = Range("a1") Sheets("Three digit numbers").Activate three = Range("a1") Sheets("Four digit numbers").Activate four = Range("a1") Sheets("Calculation").Activate Range("a2") = one + two + three + four End Sub |
For the first part of our code, we will declare the following variables: “one”, “two”, “three”, and “four” as long (numbers).
To take the value from the first sheet, we use this line of code:
|
1 |
Sheets("One digit numbers").Activate |
As seen, we call out the sheet by its declared name. Then, we define the first variable (“one”) to be equal to the value in cell A1 of our active sheet (“One digit numbers”) with this line of code:
|
1 |
one = Range("A1") |
We will repeat the same actions for every sheet and then we will get back to our sheet “Calculation” with this line of code:
|
1 |
Sheets("Calculation").Activate |
Finally, we sum all of our variables and put them in cell A2 of our sheet “Calculation” by entering the following formula:
|
1 |
Range("a2") = one + two + three + four |
When we execute our code either by pressing F5 in our module or clicking the green play button:
We will get the following results in the sheet “Calculation”:
We can use various codes to call for other sheets in our workbook. In the code above, we summed all the values located in cell A1 on every sheet. To do that, we had to call out every sheet one by one. But what if we could make a code to go and check the desired cell in every sheet that we have automatically?
To sum the values of cell A2 in every sheet in our workbook, we will use the following code:
|
1 2 3 4 5 6 7 8 9 10 |
Sub Calcuation() Dim i As Long Dim j As Long Dim ws As Worksheet j = 0 For i = 2 To ActiveWorkbook.Worksheets.Count j = j + Sheets(i).Range("b1").Value2 Next i Sheets("Calculation").Range("b2").Value2 = j End Sub |
We start by declaring three variables: “i” and “j” as long (for numbers), and “ws” as a worksheet.
We declare “j” to be 0.
Then we create our For Next Loop:
|
1 2 3 |
For i = 2 To ActiveWorkbook.Worksheets.Count j = j + Sheets(i).Range("b1").Value2 Next i |
This loop says that we should take the value located in cell b1 and store it in variable “j”, and then add the value of variable “j” to the value that was already calculated, meaning it will only add values of cell B1 from every sheet one to another.
The code starts from the second sheet. The reason why we skip the first sheet is that we would possibly like to store something there (in sheet “Calculation”, like a text or number, but we do not want this to be a part of our equation.
After the loop finishes its work, we then store the value of variable “j” in sheet “Calculation”, in cell b2:
|
1 |
Sheets("Calculation").Range("b2").Value2 = j |
When we run our code, we get the following result in our worksheet:
