How to Activate a Sheet using VBA

Let’s say...

...you are working with multiple worksheets, and for you, it’s hard to navigate to a sheet using tab.

In this situation, you can use a VBA code to activate any worksheet.

And, to write a VBA code for this you need to use Worksheet.Activate Method.

In this post, I’d like to share with you a simple way to write this code to activate or select a worksheet.

Before you write this code, you need to understand this method.

Worksheet.Activate Method

In this method, you can specify worksheet’s name or number which you want to activate.

Let’s say if you need to activate sheet1, then the code will be:

Worksheets("Sheet1").Activate

Or you can use sheet number as well.

Worksheets("1").Activate

So the final code will be:

Sub ActivateSheet1()
Worksheets("Sheet1").Activate
End Sub

Examples: Activate a Worksheet with VBA

In the real life, you can use this method in different ways. Here are some of them.

1. Activate a Worksheet on Opening

If you want to activate a specific worksheet every time when you open the workbook then you name that VBA code auto_open.

Sub auto_open()
Worksheets("Sheet1").Activate
End Sub

2. Activate a Worksheet and Hide all other

May be you want to navigate to a worksheet and hide all the other worksheets from the workbook. You can do this by using below code.

Sub HideWorksheet()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Sheet1" Then
ws.Visible = xlSheetHidden
End If
Next ws
End Sub

Change the sheet name from the above code to use it further.

Must Read Next

About the Author

Puneet Gogia

He is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

LinkedIn - YouTube

Content Protection by DMCA.com

3 thoughts

Leave a Comment

Your email address will not be published.

  1. Sorry, didn’t Understand Entirely. Guess, I have 16 worksheets in a workbook. Want to go sheet number 8 (say Sheet 8). After working in that sheet, want to go sheet number 2 (say Sheet 2). Then how use this tricks?

    Thanks in advance.