Never Do Tedious Worksheet Tasks Again With Excel VBA
The moment you discover VBA in Excel, you will never want to do another formatting or data manipulation task in Excel. When we first discovered how to use VBA, it was like getting fed ground beef after you tasted filet mignon: you just can’t go back once you have tasted heaven. Many people think VBA is meant for programmers, and we can tell you that you don’t need to be a programmer to learn how to “code” in VBA. We are going to show actual VBA code in this post for one of the most common tasks we’ve found for using VBA: cycling through worksheets.
What is VBA?
Visual Basic for Applications is a programming language that allows you to do things in Microsoft applications like Excel, Word, and PowerPoint beyond the traditional user interface. You probably know where all the menus and buttons are in Excel, and hopefully you know keyboard shortcuts to get your job done even faster in Excel. There are times when you need to do repetitive, mundane, and frankly boring tasks in Excel like coloring a cell or doing the SUM() formula on some numbers. If you are doing the same tasks every single day to the same worksheets, you are probably a good candidate to implement VBA to save you from doing these repetitive tasks.
You may have heard of macros, which are the building blocks in VBA. Macros are step-by-step procedures written in Visual Basic that let you automate repetitive tasks. In order to get started with VBA, understanding the differences between objects, properties, and methods is important.
A very basic example (pardon the pun) is a basketball. The basketball is our object. A property of the basketball, our object, could be its colors. The color can be black, white, but is typically orange. In terms of the basketball’s methods, these are actions the ball can take. For instance, the ball can bounce, roll, or maybe even pop. These are all different methods the object can do. The Microsoft Developer Network actually has a pretty good primer on getting started with VBA in Excel 2010. While that’s fun and dandy to read when you’re bored, we are all about doing and experimenting so let’s get right into the VBA code for helping you automate tasks you would do on many worksheets.
Cycle Through Worksheets VBA Code
Here is the bare bones code for quickly cycling through the worksheets in your workbook to apply some sort of operation on the cells in a given worksheet:
'Declare variables for the number of worksheets and sheet counter. Dim numsheet, y As Integer numsheet = application.Sheets.Count 'Cycle through sheets For y = 1 To numsheet Sheets(y).Select 'Here is where you would do a bunch of operations on your worksheet. range("A1").Select Next y Sheets(1).Select
It’s super simple and efficient. Line 9 is where you would enter in all the operations you would like to do on all the worksheets in your file. How does this simple script work?
We first need to figure out how many worksheets there are in your workbook, so we have a variable called numsheet that stores the number of worksheets in your file. application.Sheets is the object we are working on which essentially refers to the worksheets in your Excel file. Count is the method that we are applying to this object; the number returned is simply the number of worksheets.
In order to cycle through all the worksheets, we have to do a FOR/NEXT loop. This is where the For y = 1 To numsheet comes into play. The first time the loop runs, the variable y will equal 1. Therefore, Sheets(y).Select will be Sheets(1).Select the first time the loop runs. Sheets is the object and Select is the method we are applying to the Sheets object (which basically selects the current worksheet in your file).
range(“A1”).Select moves the cursor to cell A1 in each of your worksheets so that after you’re done applying operations to your worksheet, you’ll automatically be in cell A1 when you go back to that worksheet. Once the FOR/NEXT loop reaches the total number of worksheets in your file, it exits the loop and selects the first sheet (Sheets(1).Select) in case you have a lot of sheets in your file and want to be back in the first worksheet in your file.
Applying This Code To A Real File
Let’s say you have an Excel report where you need to make sure the first row is always highlighted yellow and bold since all your column headings are in the first row and you want your audience to notice those headings. Your Excel file has 10 worksheets that all have the same column headings. Of course, you could individually go to each worksheet and highlight the first row yellow and make the font bold, but this is the perfect task for our worksheet cycler code! So how do we make this:
Into this:
You can download the sample file here to play around yourself, but here are the steps we would take to implement this code into a macro:
- Go to your Excel preferences and make sure the “Developer” tab is checked off so that it shows up in the Ribbon
- Click on Developer=>Editor
- Right-click on your file name in the project window and select Insert=>Module
- Copy and paste the code below in the next setion into the editor and hit Save
- Run the code by clicking on Developer=>Macros=>KeyCutsWorksheetCycler=>Run
VBA Code For Highlighting The First Row Yellow And Bold Font
In the first code sample, we left a section open for where you would enter in the operations you would want to apply to your worksheet. Here is what the code would look like for the specific case of highlighting the first row yellow and applying a bold font including the subroutine lines of code to make the macro work:
Sub KeyCutsWorksheetCycler() ' This macro cycles through all your worksheets and applies a yellow fill color and bold font to the first row. 'Declare variables for the number of worksheets and sheet counter. Dim numsheet, y As Integer numsheet = Application.Sheets.Count 'Cycle through sheets For y = 1 To numsheet Sheets(y).Select Rows("1:1").Interior.ColorIndex = 6 Rows("1:1").Font.Bold = True Range("A1").Select Next y Sheets(1).Select End Sub
When you run the KeyCutsWorksheetCycler macro, Excel will cycle through all your worksheets and apply the yellow fill color and the bold font format. The actual “cycling” will happen so fast your eye may not catch it, but it’s indeed happening!
Apply A Bottom Border With Excel VBA
Let’s say you wanted to apply a bottom border to all the cells in the first row of every worksheet in your file. You would simply replace Lines 11 and 12 above with this code:
Rows("1:1").Borders(xlEdgeBottom).LineStyle = xlContinuous
The final code would look like this:
Sub KeyCutsWorksheetCycler() ' This macro cycles through all your worksheets and applies a bottom border to the first row. 'Declare variables for the number of worksheets and sheet counter. Dim numsheet, y As Integer numsheet = Application.Sheets.Count 'Cycle through sheets For y = 1 To numsheet Sheets(y).Select Rows("1:1").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("A1").Select Next y Sheets(1).Select End Sub
Conclusion
After utilizing simple formatting macros like this, we quickly realized how much time we could save on or jobs and actually do interesting work we love. Understanding VBA is also a great skill set you can add to your resume, so take the first step in learning this simple tool and it will do wonders for your work and career!
Trackbacks/Pingbacks
[…] wrote in the past about how I pretty much eliminated my job by using VBA to create hundreds of reports for me with the click of a button. It was empowering to know that I […]