Tag Archives: macro

Unhide Sheets in Excel

How to unhide sheets in Excel? Unhide all Sheets in Excel VBA

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

To unhide Sheets in Excel you need to right click on the Worksheet tab and select the Worksheet you want to unhide from the Unhide Window.

I will start by showing a way to manually unhide an Excel Worksheet using the Excel Unhide Window. Then I will show you a few tricks of how to unhide all Sheets in Excel using VBA. Lastly

Unhide Sheets in Excel

To unhide a Hidden Worksheet in Excel proceed as follows:

  1. Right click on a the Worksheets tab
  2. Click the Unhide button in the opened tab
  3. Select the Worksheet you want to unhide and click Ok

Unhide hidden Sheets in Excel

Unhide Sheet using VBA

To Unhide a single Worksheet in Excel using VBA we need to use open the Visual Basic Editor. To do this quickly simply us this Excel Keyboard shortcut ALT+F11.

You can type the below in the Immediate window and hit Enter:

Where NameOfWorksheet is obviously the name of the Worksheet you want to Unhide in Excel.

Hide Sheet using VBA

If you want to Hide a Worksheet in Excel using VBA you can modify the code above. Instead of using xlSheetVisible just use xlSheetHidden:

This will hide the worksheet named NameOfWorksheet.

Unhide All Sheets in Excel using VBA

To unhide all Sheets in Excel we must revert to VBA as there is no other way to select multiple worksheets using the built in Excel Unhide window. Again let us open the Visual Basic Editor by using the Excel Keyboard shortcut ALT+F11. Next in the Immediate Window let us type:

Unhide all Sheets in Excel using VBA
Unhide all Sheets in Excel using VBA

Below the same code as above but spread across multiple lines. Let us run through this:

Unhide all Sheets by Name

In many cases you don’t necessarily want to Unhide all Sheets in Excel. Instead you might want to Unhide only a subset of the Hidden Worksheets using a name pattern.

Assume you want to Unhide all Worksheets that fall into a certain pattern where part of the name can be any sequence of characters. For this we can amend the code above using the VBA Like operator:

Similarly as above we can wrap it up to a oneliner to run in the Immediate Window:

The code above will unhide all Worksheets which name starts with Hidden and suffixed by any number of characters e.g. numbers like in the example below:
Hidden worksheets Excel

What is happening is using the VBA For Each loop we are iterating through the VBA Collection of Worksheets. When a certain Worksheet name matches our VBA Like function statement we make it visible.

Button to Hide/Unhide Sheets

Lastly to learn how to Unhide Sheets in Excel we will sum up what we have learned and make a simple VBA UserForm to be able to quick manage visibily across the entire list of Excel Worksheets.

Create the UserForm

First you need to create a VBA UserForm with a VBA ListBox and a VBA CommandButton:
Manage Sheets UserForm

Program Initialize and Button Click

If you named your objects correctly past the following code into the VBA UserForm source code:

Run the UserForm

To put our code to the test all we need to do is create and show the ManageWorksheets VBA UserForm. The following VBA Procedures code needs to created in a VBA Module:

When executed this is how it will look like:
Unhide Multiple Sheets in Excel

VBA Tricks

VBA Tips and Tricks

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

Instant Print in the Immediate Window

Use the “?” preceding a line of code that can evaluate to a value in the VBE Immediate Window. Hit Enter to evalute the code in the next line. See example below:

VBA Trick Use question mark for instant Debug Print
VBA Trick Use question mark for instant Debug Print

This is a VBA Trick I was not aware for a lot time but saves a lot of time when debugging code or trying to evaluate a single line or bit of code without needing to debug a whole functions or procedure.

If you are not familiar with the Immediate Window read my VBE Project tutorial

Evaluate formulas shortcut

One of my favorite VBA Trick is using the evaluate function using square brackets “[]”. If you want to read more read my VBA Evaluate article.

Evaluate works both on:

  • Evaluation of Named Ranges
  • Evaluation of Excel Worksheet Functions

Evaluating Named Ranges in VBA

Here’s an example of Named Range evaluation in VBA:

VBA Trick: VBA Evaluate Ramed Range
VBA Trick: VBA Evaluate Ramed Range

Evaluating Worksheet Functions in VBA

Here’s an example of Worksheet Function evaluation in VBA:

VBA Trick VBA Evaluate Worksheet Function
VBA Trick VBA Evaluate Worksheet Function

This is one of the most useful trick as VBA does not allow you to otherwise easily do a lookup of data is a worsheet.

Remeber to always use the “,” list separator when defining the arguments as VBA is not not dependent on your system country settings even if you separate arguments in regular Excel worksheet functions.

Use VBA. to view all VBA Built in functions and constants

Often you stumble not being able to remind yourself of that one useful VBA Function and need to search the function online. A great VBA Trick is to type in “VBA.” and then you will be provided with a list of available VBA Functions and Constants.

VBA Trick Use VBA to list all built in functions and constants
VBA Trick Use VBA to list all built in functions and constants

User Defined Functions

Excel has a multitude of functions, half of which you probably don’t know. However, what happens when you lack a particular function or want to save time bundling up common functions? Let me introduce you to the little known world of User Defined Functions (UDF).

Simple UDF

A simple UDF can be found below:
VBA Trick Simple UDF

Advanced UDF

Let us not try a more advanced UDF showing how powerful it can be. First I will create a simple table:

VBA Trick UDF Table
VBA Trick UDF Table

Now what I need is to somehow get a number of matches or these records against various criteria e.g. sex, sales, age, country. One way would be to go for a Pivot Table but sometimes a simple UDF can be more useful especially if we want to embed this value in your calculations.

So I will create a UDF that takes these arguments and provides the number of matches:

Now we can use the UDF Function in Excel:

VBA Trick Complex UDF
VBA Trick Complex UDF

Speeding up your VBA Macros

Your macro running slow? A lot of CPU overhead goes into updating your screen in case and recalculations of formulas impacted by your macros. With a simple VBA Trick you can turn off ScreenUpdating and Calculations until your macro is done.

What to do when your macros needs to run for a long time and you need to update your screen every now and then? Use the DoEvents procedure to refresh your screen only in selected intervals.
Read here how learn of more ways of optimizing your VBA performance.

excel vba evaluate

Excel VBA Evaluate – Tips and Tricks to use Application.Evaluate

1 Star2 Stars3 Stars4 Stars5 Stars (7 votes, average: 4.86 out of 5)
Loading...

Some call it the most powerful command in Excel VBA, while others struggle with simple VBA macros oblivious of its existence – today we with explore the VBA Evaluate function or more precisely the VBA Application.Evaluate function.

We will start with the basics and then move on to more elaborate examples and uses of the VBA Evaluate function.

VBA Evaluate

The Excel VBA Evaluate function converts a Microsoft Excel Name to an object or a value.

Syntax

The syntax for the Evaluate function in VBA is:

Parameters

Name
A formula or the Name of the object you want to evaluate. The length of the Name must be less than or equal to 255 characters.

Examples

Example 1: Simple formula evaluation

Example 2: Using Excel cells in formula evaluation

What out for the first example above! As it might evaluate differently depending on the Activeworksheet!

Example 3: Using functions in formula evaluation

vlookup table
This example is going to be a bit more complex for several reasons I will explain after the example.

Consider the table on the right I will use to run a simple VLOOKUP function. Let us pull up the value associated with the lookup name “Andrew”.

Now there are several things you need to learn from the example above:

  • Escaping strings enclosed within double apostrophes with another set of apostrophes “” – otherwise expect errors
  • Use a comma (,) to separate all function arguments REGARDLESS of your default system list separator as VBA assumes the comma by default
  • You don’t need an equals sign before your formula. The Excel VBA Evaluate functions assumes this by default

Now that we know the basics of the Excel VBA Evaluate function lets us explores some useful tips:

Use Evaluate brackets instead of Range

Did you know you can encapsulate Excel formulas in VBA in brackets to achieve the same results are embracing them within the Evaluate function? See example on the right. The Evaluate function can be easily swapped with square brackets to achieve same resutl.

You can easily replace a VBA Range with square brackets as well:

CYou can seriously shorten your VBA code:

Array definition using VBA Evaluate

You can also define elements in a VBA Array in just a single line without needing to resort to the VBA Substring function like so:

Summary

The VBA Evaluate function is a very powerful tool if you want to easily reference cells in your workbook in your VBA Macros. There are however many pitfalls in using this function as well as its square brackets equivalent. Here are some:

  • Prone to errors – typical error includes forgetting that without preceding cells with the worksheet name the statement with always reference the Activesheet
  • Slightly slower – if you intend to run your code multiple times you might see a slight performance disadvantage. Usually you won’t notice this at all

In summary I personally think it make sense to know the VBA Application.Evaluate function as sometimes quick and dirty is all you need. However, I would avoid using it in complex VBA macros as it is easy to make a mistake, this comes especially as I am not a fan of embedding static cell references e.g. Range(“A1”) vs Cells(1,1). What is your take?

vba last row featured

Excel VBA Last Row, Last Column, Last Cell

1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 4.00 out of 5)
Loading...

Today I am going to take on one of the most frequent question people ask about Excel VBA – how to the the last row, column or cell of a spreadsheet using VBA. The Worksheet range used by Excel is not often the same as the Excel last row and column with values. Therefore I will be careful to explain the differences and nuisances in our quest to find the last row, column or cell in and Excel spreadsheet.

CONTENTS

VBA Last Row

The Last Row may as be interpreted as:

Last Row in a Column

vba last row
Last Row in a Column

To get the Last Row with data in a Column we need to use the End property of an Excel VBA Range.

Dim lastRow as Range

'Get Last Row with Data in Column
Debug.Print Range("A1").End(xlDown).Row 'Result: 5
Set lastRow = Range("A1").End(xlDown).EntireRow

'Get Last Cell with Data in Row
Dim lastRow as Range
Set lastRow = Range("A1").End(xlDown)

Last Row with Data in Worksheet

vba last used row
Last Used Row with Data in Worksheet

To get the Last Row with data in a Worksheet we need to use the SpecialCells or Find properties of an Excel VBA Range.

Dim lastRow as Range, ws As Worksheet
Set ws = ActiveSheet

'Get Last Row with Data in Worksheet using SpecialCells
Debug.Print ws.Cells.SpecialCells(xlCellTypeLastCell).Row
Set lastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).EntireRow 

'Get Last Row with Data in Worksheet using Find
Debug.Print Debug.Print ws.Cells.Find(What:="*", _
        After:=ws.Cells(1), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).Row
Set lastRow = Debug.Print ws.Cells.Find(What:="*", _
        After:=ws.Cells(1), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).EntireRow

Last Row in Worksheet UsedRange

vba last used row usedrange
Last Used Row in Worksheet UsedRange

To get the Last Row in the Worksheet UsedRange we need to use the UsedRange property of an VBA Worksheet.

'Get Last Row in Worksheet UsedRange
Dim lastRow as Range, ws As Worksheet
Set ws = ActiveSheet
Debug.Print ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row
Set lastRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).EntireRow
The UsedRange represents a Range used by an Excel Worksheet. The Used Range starts at the first used cell and ends with the most right, down cell that is used by Excel. This last cell does not need to have any values or formulas as long as it was edited or formatted in any point in time

VBA Last Column

The Last Column may as be interpreted as:

Last Column with Data in a Row

vba last column
Last Column in Row

To get the Last Column with data in a Row we need to use the End property of an Excel VBA Range.

Dim lastColumn as Range

'Get Last Column with Data in Row
Debug.Print Range("A1").End(xlRight).Row 'Result: 5
Set lastColumn = Range("A1").End(xlRight).EntireColumn

'Get Last Cell with Data in Column
Dim lastColumn as Range
Set lastColumn = Range("A1").End(xlRight)

Last Column with Data in Worksheet

vba last used column
Last Column with Data in a Worksheet

To get the Last Column with data in a Worksheet we need to use the SpecialCells or Find properties of an Excel VBA Range.

Dim lastColumn as Range, ws As Worksheet
Set ws = ActiveSheet

'Get Last Column with Data in Worksheet using SpecialCells
Debug.Print ws.Cells.SpecialCells(xlCellTypeLastCell).Column
Set lastColumn = ws.Cells.SpecialCells(xlCellTypeLastCell).EntireColumn 

'Get Last Column with Data in Worksheet using Find
Debug.Print Debug.Print ws.Cells.Find(What:="*", _
        After:=ws.Cells(1), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).Column
Set lastColumn = Debug.Print ws.Cells.Find(What:="*", _
        After:=ws.Cells(1), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).EntireColumn

Last Column in Worksheet UsedRange

vba last used column usedrange
Last Used Column in Worksheet UsedRange

To get the Last Column in the Worksheet UsedRange we need to use the UsedRange property of an VBA Worksheet.

'Get Last Column in Worksheet UsedRange
Dim lastColumn as Range, ws As Worksheet
Set ws = ActiveSheet
Debug.Print ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column
Set lastColumn = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).EntireColumn
The UsedRange represents a Range used by an Excel Worksheet. The Used Range starts at the first used cell and ends with the most right, down cell that is used by Excel. This last cell does not need to have any values or formulas as long as it was edited or formatted in any point in time

VBA Last Cell

The Last Cell may as be interpreted as:

Last Cell in a series of data

vba last cell in data series
Last Cell in data series

To get the Last Cell in a series of data (table with non-blank values) we need to use the End property of an Excel VBA Range.

Dim lastCell as Range

'Get Last Cell in a series of data
Dim lastCell as Range
Set lastCell = Range("A1").End(xlRight).End(xlDown)
Debug.Print "Row: " & lastCell.row & ", Column: " & lastCell.column

Last Cells with Data in Worksheet

vba last cell in worksheet
Last Cell with data in Worksheet

To get the Last Cell with data in a Worksheet we need to use the SpecialCells or Find properties of an Excel VBA Range.

Dim lastCell as Range, ws As Worksheet
Set ws = ActiveSheet

'Get Last Cell with Data in Worksheet using SpecialCells
Set lastCell = ws.Cells.SpecialCells(xlCellTypeLastCell)
Debug.Print "Row: " & lastCell.row & ", Column: " & lastCell.column

'Get Last Cell with Data in Worksheet using Find
Set lastColumn = Debug.Print ws.Cells.Find(What:="*", _
        After:=ws.Cells(1), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False)
Debug.Print "Row: " & lastCell.row & ", Column: " & lastCell.column

Last Cell in Worksheet UsedRange

vba last cell in usedrange
Last Cell in UsedRange

To get the Last Cell in the Worksheet UsedRange we need to use the UsedRange property of an VBA Worksheet.

'Get Last Cell in Worksheet UsedRange
Dim lastCell as Range, ws As Worksheet
Set ws = ActiveSheet

Set lastCell = ws.UsedRange.Cells(ws.UsedRange.Rows.Count,ws.UsedRange.Columns.Count)
Debug.Print "Row: " & lastCell.row & ", Column: " & lastCell.column
The UsedRange represents a Range used by an Excel Worksheet. The Used Range starts at the first used cell and ends with the most right, down cell that is used by Excel. This last cell does not need to have any values or formulas as long as it was edited or formatted in any point in time

VBA UsedRange

vba usedrange
The UsedRange in a Worksheet with not Values or Formulas

The VBA UsedRange represents the area reserved and saved by Excel as the currently used Range on and Excel Worksheet. The UsedRange constantly expands the moment you modify in any way a cell outside of the previously Used Range of your Worksheet.

The UsedRange is not reduced if you Clear the Contents of Range. The only way to reduce a UsedRange is to delete the unused rows and columns.

How to check the UsedRange

The easiest way to check the currently UsedRange in an Excel Worksheet is to select a cell (best A1) and hitting the following key combination: CTRL+SHIFT+END. The highlighted Range starts at the cell you selected and ends with the last cell in the current UsedRange.

Often bloated, large Excel files are effects of misuse of UsedRange. If you want to know more about reducing Excel file size read my post

Check UsedRange in VBA

Use the code below to check the area of the UsedRange in VBA:

Dim lastCell As Range, firstCell As Range, ws As Worksheet
Set ws = ActiveSheet
    
Set lastCell = ws.UsedRange.Cells(ws.UsedRange.Rows.Count, ws.UsedRange.Columns.Count)
Set firstCell = ws.UsedRange.Cells(1, 1)
Debug.Print "First Cell in UsedRange. Row: " & firstCell.Row & ", Column: " & firstCell.Column
Debug.Print "Last Cell in UsedRange. Row: " & lastCell.Row & ", Column: " & lastCell.Column    

For the screen above the result will be:

First Cell in UsedRange; Row: 2, Column: 2
Last Cell in UsedRange; Row: 5, Column: 6

First UsedCell in UsedRange

The below will return get the first cell of the VBA UsedRange and print its row and column:

Dim firstCell as Range
Set firstCell = ws.UsedRange.Cells(1, 1)
Debug.Print "First Cell in UsedRange. Row: " & firstCell.Row & ", Column: " & firstCell.Column

Last UsedCell in UsedRange

The below will return get the first cell of the VBA UsedRange and print its row and column:

Dim lastCell as Range
Set lastCell = ws.UsedRange.Cells(ws.UsedRange.Rows.Count, ws.UsedRange.Columns.Count)
Debug.Print "Last Cell in UsedRange; Row: " & lastCell.Row & ", Column: " & lastCell.Column
excel vba worksheets tutorial

Excel VBA Worksheets VBA Activesheet vs Worksheets, Select, Activate etc.

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.50 out of 5)
Loading...

Today we are going to learn about VBA Worksheets. We will cover all the nuisances e.g. VBA Activesheet and how it compares to regular Worksheets, how to Select Worksheets, how to Activate Worksheets, Selecting vs Activating Worksheets… and everything else you need to know about the VBA Worksheet in general.

ThisWorkbook vs ActiveWorkbook

Some Excel Worksheets
Some Excel Worksheets
Lets start with the basics. Before we start I want to stress and remind the difference between ActiveWorkbooks and ThisWorksbooks. In short:

  • ThisWorkbook – refers to the Workbook in which the VBA macro is running
  • ActiveWorkbook – refers to the Workbook which is in the topmost Excel Window

It is extra important to understand this difference and I encourage you to read my post on this topic first.

The Excel VBA Object Hierarchy

Excel vba object hierarchySecondly it makes sense to remind the Excel Object hierarchy.

At the top, at the root we have our Excel Application. The Excel Application represents the entire Excel process. Further down the tree we have our Workbooks. Each Application contains a collection of Workbooks. Looking into a single Workbook we will notice it contains a collection of Worksheets. Worksheets on the other hand as you know can define Ranges (not the same a single cells). Using a Range we can access its cells Values or Formulas.

Accessing VBA Worksheets

Now that we have that behind us lets explore the different ways in which we can access Worksheets in VBA:

ActiveWorkbook VBA Worksheets

The Sheets and Worksheets collections

Sheets within the ActiveWorkbook:

Dim ws as Worksheet, wsCollection as Sheets
Set wsCollection = Sheets 'Get entire collection of Worksheets
Set ws = Sheets(1) 'Get first Worksheet in ActiveWorkbook
Set ws = Sheets("Sheet1") 'Get Worksheet named "Sheet1" in ActiveWorkbook

Similarly we can use Worksheets instead of Sheets.

Dim ws as Worksheet, wsCollection as Sheets
Set wsCollection = Worksheets 'Get entire collection of Worksheets
Set ws = Worksheets(1) 'Get first Worksheet in ActiveWorkbook
Set ws = Worksheets("Sheet1") 'Get Worksheet named "Sheet1" in ActiveWorkbook

ThisWorkbook VBA Worksheets

The Sheets and Worksheets collections

Sheets within the ThisWorkbook:

Dim ws as Worksheet, wsCollection as Sheets
Set wsCollection = Sheets 'Get entire collection of Worksheets
Set ws = ThisWorkbook.Sheets(1) 'Get first Worksheet in ThisWorkbook
Set ws = ThisWorkbook.Sheets("Sheet1") 'Get Worksheet named "Sheet1" in ThisWorkbook

Similarly we can use Worksheets instead of Sheets.

Dim ws as Worksheet, wsCollection as Sheets
Set wsCollection = Worksheets 'Get entire collection of Worksheets
Set ws = ThisWorkbook.Worksheets(1) 'Get first Worksheet in ActiveWorkbook
Set ws = ThisWorkbook.Worksheets("Sheet1") 'Get Worksheet named "Sheet1" in ThisWorkbook

Worksheet VBA Name vs Excel Name

caption id=”attachment_11373″ align=”alignright” width=”213″]Worksheet VBA Name vs Excel Name Worksheet VBA Name vs Excel Name[/caption]When speaking about Worksheets in ThisWorkbook, the VBA Name of a Worksheet is not the same as the Excel Name of a Worksheet. Let us understand this different. On the right we have a screen from an example Workbook. The VBAName string is the VBA Name of our Worksheet, on the other hand the Excel Name string is our Excel Name. You can use both names to refer to the same Worksheet but in different ways.

Using the VBA Name of a Worksheet

Worksheet VBA Name
Worksheet VBA Name
We can refer to a VBA Worksheet by its VBA Name directly – just by typing it. This is very convenient and a good practice. That is because the VBA Name can’t be changed by a user by mistake from the level of Excel (not the VBE). Hence whatever name the user give from the Workbook level to your Worksheet – its VBA Name stays the same.

Using the Excel Name of a Worksheet

Referring to an Excel Worksheet from VBA is not a recommended practice. Below I am referring to the Worksheet I named Excel Name in my example above.

Dim ws as Worksheet

Set ws = Worksheets("Excel Name") 'Get Worksheet named "Sheet1" in ActiveWorkbook

Not what you notice is that compared to acquiring the Worksheet by its VBA Name, when using the defaults Worksheets or Sheets object you land with the said Worsheet, but from the ActiveWorkbook. Usually this doesn’t do much of a difference. But I am highlighting that this is simply another place to make a common mistake. See both versions below:

Dim ws as Worksheet

'---Sheet by Excel Name in ActiveWorkbook---
Set ws = Worksheets("Excel Name") 'Get Worksheet named "Sheet1" in ActiveWorkbook
Set ws = ActiveWorkbook.Worksheets("Excel Name") 'Get Worksheet named "Sheet1" in ActiveWorkbook

'---Sheet by Excel Name in ThisWorkbook---
Set ws = ThisWorkbook.Worksheets("Excel Name") 'Get Worksheet named "Sheet1" in ActiveWorkbook

The VBA ActiveSheet

As with Active vs ThisWorkbook you need to first understand the difference between Selecting a Worksheet and Activating it.

Selected vs Activated Worksheet, the differences:

  • Selected Worksheet – one or more Worksheets that have been selected within an Excel Window. Each Workbook has its own set of Selected Worksheets
  • ActiveWorksheet – the current Worksheet you are viewing and working in. The only top-most Worksheet in all Application Workbooks

From the definition above you see that there can be more than 1 Selected Worksheet, while only 1 ActiveSheet. In fact both are totally different things. Lets explore this in some examples:

Example 1: Explaining ActiveSheet

vba worksheets exampleLet us say we have 2 Workbooks open. One is Book1.xlsm and there other is Book2.xlsm.

Lets open VBE in Book1.xlsm. We will add the following piece of code:

Sub TestActiveSheet()
 MsgBox ActiveSheet.Name
End Sub

Now lets activate Book2.xlsm. Rename any Worksheet e.g. to MyActiveWorksheet. Next go to the Developer ribbon select Macros and run Books1.xlsm!TestActiveSheet. What happened? You probably got something like this:
vba activesheet
Although you ran a macro from Book1.xlsm, VBA considers always the top-most worksheet as the ActiveSheet.

Example 2: Explaining Selected vs ActiveSheet

Let us consider the same example. Open Book1.xlsm and add the following code to the VBE:

Sub SelectedVSActive()
  Dim res As String, ws As Worksheet
  res = "Selected sheets:" & vbNewLine
  For Each ws In ActiveWindow.SelectedSheets
    res = res & ws.Name & vbNewLine
  Next ws
  MsgBox res & vbNewLine & "ActiveSheet:" & vbNewLine & ActiveSheet.Name
End Sub

Now do the following:

  • Activate workbook Book2.xlsm
  • Select a number of Worksheets (at least 2)
  • Run Book1.xlsm!SelectedVSActive macro

What happens? You should get something like this:
vba activesheet vs selected
You see that:

  • Only the top-most Worksheet is considered as the ActiveSheet
  • The ActiveSheet is ALWAYS considered as a Selected Worksheet within the ActiveWindow (this last part is important)

What happens if you change ActiveWindow to e.g. Windows(2)? You will see a different set of Selected Worksheets. Why? Because all Workbooks have Selected Worksheets. Hope you see the difference.

Activating VBA Worksheets

Activating Worksheet is easy in VBA. You do it using the Activate property of Worksheets. How to activate any Worksheet using VBA?

Dim ws as Worksheet
'...Set ws to some Worksheet
ws.Activate

Activate Worksheet examples

A few examples below:

Sheets(1).Activate 'Activate first Worksheet in ActiveWorkbook
Sheet1.Activate 'Activate Sheet1 in ThisWorkbook
Worksheets("MyNamedWorksheet").Activate 'Activate Excel named Worksheet in ActiveWorkbook

Selecting VBA Worksheets

Selecting Worksheets is a little more difficult as we may want to select a single Worksheet or more.

Selecting a single VBA Worksheet

Selecting a single VBA Worksheet is simple. Just use the Select property of a Worksheet.

Dim ws as Worksheet
'...Set ws to some Worksheet
ws.Select

Selecting a single Worksheet examples

A few examples below:

Sheets(1).Select 'Select first Worksheet in ActiveWorkbook
Sheet1.Select 'Select Sheet1 in ThisWorkbook
Worksheets("MyNamedWorksheet").Select 'Select Excel named Worksheet in ActiveWorkbook

Selecting multiple VBA Worksheets

Selecting more than 1 Worksheet is a little more complex as we need to leverage the VBA Array function:

Sheets(Array(1, 2)).Select 'Select first and second Worksheet in ActiveWorkbook
Sheets(Array("Named1", "Named2")).Select 'Select 2 named Worksheets in ActiveWorkbook

This is not the most elegant way of doing it. Fortunately the Select function has an argument called Replace. Setting it to false will mean that the previously selected is not to be deselected. Thus we can extend our selections like this:

Sheets(1).Select 
Call Sheets(2).Select(False)
Call Sheets(3).Select(False)

This will select the first three Worksheets in our ActiveWorkbook.

Deleting VBA Worksheets

To delete a VBA Worksheet we need to turn off the Application.DisplayAlerts

Application.DisplayAlerts = False
Sheets(1).Delete
Application.DisplayAlerts = True

You can delete multiple Worksheets in a similar fashion as how you can do a multiple Worksheet Select:

Application.DisplayAlerts = False
Sheets(Array(1, 2)).Delete
Application.DisplayAlerts = True

Copying VBA Worksheets

You can copy Worksheets within a Workbook or from other open Workbooks.

'Copy Sheet1 and paste before Sheet2
Worksheets("Sheet1").Copy Before:=Sheets("Sheet2")

'Copy Sheet1 and paste before Sheet2 in Workbook Book1.xlsm
Worksheets("Sheet1").Copy Before:=Workbooks("Book1.xlsm").Sheets("Sheet2")

Moving VBA Worksheets

You can move Worksheets within a Workbook or from other open Workbooks.

'Move Sheet1 and paste before Sheet2
Worksheets("Sheet1").Move Before:=Sheets("Sheet2")

'Move Sheet1 and paste before Sheet2 in Workbook Book1.xlsm
Worksheets("Sheet1").Move Before:=Workbooks("Book1.xlsm").Sheets("Sheet2")

Other VBA Worksheets properties

Below are other VBA Worksheet properties worth mentioning:

Worksheet property Description Example
Calculate Calculates all dirty formulas on the current Worksheet
Dim ws as Worksheet
'...Set ws
ws.Calculate
Name Get or set the Excel Name of the Worksheet
Dim ws as Worksheet
'...Set ws
Debug.Print ws.Name 'Print Excel Name of Worksheet
ws.Name = "New name" 'Set new name for Worksheet
Visible Display, Hide or make Worksheet VeryHidden. Possible values:

  • xlSheetVisible
  • xlSheetVeryHidden
  • xlSheetHidden
Dim ws as Worksheet
'...Set ws
ws.Visible = xlSheetHidden 'Make ws Worksheet Hidden

To learn more read Unhide sheets in Excel / Unhide all Sheets in Excel using VBA.