How to Activate a Sheet using VBA

puneet-gogia-excel-champs

- Written by Puneet

Let’s say you are working with multiple worksheets, and for you, it’s hard to navigate to a sheet using a 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 the worksheet’s name or number which you want to activate. Let’s say you need to activate sheet1, then the code will be:

Worksheets("Sheet1").Activate

Or you can use sheet numbers 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 Su

2. Activate a Worksheet and Hide all other

Maybe you want to navigate to a worksheet and hide all the other worksheets from the workbook. You can do this by using the 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.

Last Updated: June 01, 2023

11 thoughts on “How to Activate a Sheet using VBA”

  1. Hi,
    Can someone help me below is my macro code is opening a new file and saving it.

    I want to amend this to save the output in same file as multiple sheet based on the list define in RC sheet.

    basically i am copying the
    Sub CreateAll()

    Application.DisplayAlerts = False

    Set rcbook = Windows(“RC.xlsx”)
    rcbook.Activate

    lastrow = ActiveWorkbook.Worksheets(“Sheet2”).Range(“A200”).End(xlUp).Row

    For i = 1 To lastrow

    rcbook.Activate
    valrc = Worksheets(“Sheet2”).Range(“A” & i).Value

    ThisWorkbook.Activate
    Range(“C2:N2″).Value = valrc

    ActiveWorkbook.SaveAs Filename:= _
    ” & valrc & “.xlsm” _
    , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

    Next i

    Application.DisplayAlerts = True

    End Sub

    Reply
  2. Hi there ,
    I am below average user of excel and a programmer of Harbour and want to add some formatting to excel sheet can you guide about or a help file to get it , Like merge of cells changing backgroud (fill) color of cell, I am able to change text colour etc.
    Thanks

    Reply
  3. FYI Puneet

    I’m doing a class lab inventory. I created a macros that one click it can show the database available. But I want to hide this data.xlsx. from borrower. But if i manually hide the data.xlsx , the macros can’t work. So could you advice on this matter.

    Sub changeSh()
    ‘Application.ScreenUpdating = True its true by default

    Dim wkb As Workbook
    Set wkb = ThisWorkbook

    wkb.Sheets(“Data”).Activate

    End Sub

    Reply
  4. Ajay, I would first ask why password protect multiple workbooks? Should these not be protected individually? There are several ways in which a workbook can be protected. Would you be protecting them from being edited? If so, can others see a read only version or not? I think you can see your question has not been developed fully.

    Reply
  5. Ali, the answer to you question would be to set up a keyboard shortcut. You would need to assign a different shortcut to each worksheet. I would keep it simple. Using the example above I would use Shift+Ctrl+A. Then, for worksheet 2 I would use Shift+Ctrl+B.

    Reply
  6. 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.

    Reply

Leave a Comment