Set Worksheets in VBA

  1. Set Worksheet in VBA
  2. Use the ActiveSheet Method to Set a Variable in VBA

We will introduce how to set worksheets in VBA with different methods.

Set Worksheet in VBA

VBA is a programming language specifically used with Excel but can perform other tasks, such as automation tasks.

When working with Excel sheets in VBA, it is difficult to remember multiple sheets and use them correctly. To overcome this problem, we can use the objects in VBA.

We can set the sheets to an object, remember the objects, and use them according to our needs. It is easier to remember objects because we can name them whatever we want, and we can easily remember for what purpose it was used.

To set the sheet to any objects, we first need to declare the object using the keyword Dim. After declaring the variable, we can use the Set keyword to set the sheet to that variable, as shown below.

Dim newSheet As Worksheet
Set newSheet = ThisWorkBook.Sheets("Sheet1")

Use the ActiveSheet Method to Set a Variable in VBA

There are some situations in which we only want to use the active sheet in our VBA code to perform the tasks. For this purpose, we can use the ActiveSheet method, set the active sheet to a variable, and use it for later purposes.

This method gives us direct access to the currently active worksheet. We can use the following code to set the worksheet using the ActiveSheet method.

Dim book As Workbook: Set book = ThisWorkbook
Dim sheet As Worksheet
Set sheet = book.ActiveSheet
Write for us
DelftStack articles are written by software geeks like you. If you also would like to contribute to DelftStack by writing paid articles, you can check the write for us page.

Related Article - VBA Worksheet

  • Activate Worksheet in Excel in VBA