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 you will feel like after learning the quick VBA tip in this post.

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:

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!