VBA Spreadsheets - The Definitive Guide - Automating Excel (2023)

In this article

  • Sheets Vs. Worksheets
  • reference sheets
    • active sheet
    • numberblatt
    • Sheet index number
    • Blatt „Code name“
    • Reference sheets in other workbooks
  • Activate vs Select sheet
    • activate a sheet
    • pick a sheet
    • Select multiple sheets
  • worksheet variables
  • Iterate through all sheets in the workbook
  • worksheet protection
    • book protection
    • worksheet protection
    • protect worksheet
    • Unprotect the spreadsheet
  • Visible Properties Spreadsheet
    • show worksheet
    • Hide worksheet
    • Hide a lot of worksheet
  • Worksheet Level Events
    • Worksheet Activation Event
    • worksheet change event
  • Worksheet Cheat Sheet
  • Cheatsheet for VBA worksheets

This is the ultimate guide to working with Excel Spreadsheets/Spreadsheets in VBA.

At the end of this guide, we've created a cheat sheet with common commands for working with Sheets.

Sheets Vs. Worksheets

There are two ways to reference Sheets with VBA. The first is with the leaves.Object:

Sheets("Sheet1").Activate

The other is with the Worksheets object:

Worksheets ("Sheet1").Activate

99% of the time these two objects are identical. In fact, if you've searched onlineExamples of VBA code, you've probably seen both objects used. Here is the difference:

The Sheets collection contains spreadsheets AND chart sheets.

VBA Spreadsheets - The Definitive Guide - Automating Excel (1)

Therefore, use Sheets if you want to include regular worksheets AND chart sheets. Use spreadsheets when you want to exclude chart sheets. For the rest of this guide, we'll use sheets and worksheets interchangeably.

reference sheets

There are several ways to refer to leaves:

  • active sheet
  • Sheet tab name
  • Sheet index number
  • Blatt code name

active sheet

Heactive sheetis the sheet that is currently active. In other words, if you paused your code and looked at Excel, the sheet is visible. The following code example shows amessage boxwith the name ActiveSheet.

(Video) Learn Excel Reporting Automation For Beginners with Excel VBA (Code Included)

MsgBox ActiveSheet.Nombre

numberblatt

You're probably most familiar with cue sheets by their tab name:

VBA Spreadsheets - The Definitive Guide - Automating Excel (2)

Sheets("TabName").Activate

This is the name of the sheet visible to Excel users. Enter it as a quoted string in the Sheets object.

Sheet index number

The sheet index number is the position of the sheet in the workbook. 1 is the first sheet. 2 is the second sheet, etc.:

VBA Spreadsheets - The Definitive Guide - Automating Excel (3)

Sheets(1).Enable

Sheet index number: last sheet of the book

To reference the last sheet in the workbook, useSheets.Countto get the latest index number and activate this sheet:

Sheets(Sheets.Number).Enable

Blatt „Code name“

The code name of the sheet is its object name in VBA:

VBA Spreadsheets - The Definitive Guide - Automating Excel (4)

CodeName.Activate

VBA coding made easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA code generator that allows beginners to code procedures from scratch with minimal programming knowledge and with many time-saving features for all users.

Learn more

Reference sheets in other workbooks

It's also easy to reference sheets in other workbooks. To do this, you must use thebook object:

(Video) Excel VBA Beginner Tutorial

Workbooks("VBA_Examples.xlsm").Worksheets("Sheet1").Activate

Important:HeThe workbook must be openbefore you can consult their sheets.

Activate vs Select sheet

In another article we will talk about everything about it.Activate and select sheets. The short version is this:

When you activate a sheet, it becomes an ActiveSheet. This is the sheet you would see if you looked at your Excel program. Only one sheet can be activated at a time.

activate a sheet

Sheets("Sheet1").Activate

When you select a sheet, it also becomes an ActiveSheet. However, you can select multiple sheets at once. If multiple sheets are selected at once, the "top" sheet is the ActiveSheet. However, you can toggle the ActiveSheet within selected sheets.

VBA Programming | The code generator works for you!

pick a sheet

Sheets("Sheet1").Select

Select multiple sheets

one firstFilaTo select multiple sheets at once:

Worksheets(Array("Sheet2", "Sheet3")).Select

worksheet variables

Assign a worksheet to aobject variableallows you to reference the worksheet by its variable name. This can save a lot of typing time and make your code easier to read. There are also many other reasons why you might want to use variables.

To declare a spreadsheet variable:

dim ws as worksheet

Assign a worksheet to a variable:

Set ws = Sheets("Sheet1")

Now you can query the worksheet variable in your code:

ws.Activate

Iterate through all sheets in the workbook

Spreadsheet variables are useful if you want toCycle through all worksheets in a workbook. The easiest way to do this is:

Dim ws como WorksheetFor Each ws en Worksheets MsgBox ws.nameNext ws

This code iterates through all the worksheets in the workbook and displays the name of each worksheet in a message box. Iterating through all sheets in a workbook is very useful when you lock/unlock or hide/show multiple worksheets at once.

(Video) Excel 2019 VBA Full Course Tutorial (7+ Hours)

worksheet protection

AutoMacro | Ultimate VBA Plugin | Click for a free trial!

book protection

VBA Spreadsheets - The Definitive Guide - Automating Excel (6)

book protectionlock the bookagainst structural changes like adding, deleting, moving or hiding worksheets.

You can enable workbook protection using VBA:

ActiveWorkbook.Protect Password:="Password"

or disable workbook protection:

ActiveWorkbook.UnProtect Contraseña:="Kennwort"

Note: You can also protect/unprotect without a password by omitting the password argument:

ActiveWorkbook.Protect

worksheet protection

Worksheet-level protection prevents changes to individual worksheets.

protect worksheet

Worksheets ("Sheet1"). "Password" protection

Unprotect the spreadsheet

Worksheets ("Sheet1"). Unprotect "Password".

There are a variety of options to protect worksheets (allow formatting changes, allow user to insert rows, etc.). We recommend using the macro recorder to record the desired settings.

We will discuss spreadsheet protection in more detail.Here.

AutoMacro | Ultimate VBA Plugin | Click for a free trial!

Visible Properties Spreadsheet

you may already knowWorksheets can be hidden:

VBA Spreadsheets - The Definitive Guide - Automating Excel (7)

(Video) 🤖 Excel Macros & VBA - Tutorial for Beginners

There are actually three visibility settings for worksheets: Visible, Hidden, andvery hiddenAny normal Excel user can show hidden sheets by right-clicking on the sheet tab area (see above). VeryHidden sheets can only be displayed with VBA code or in the VBA editor. Use the following code examples to show and hide worksheets:

show worksheet

Worksheets("Sheet1").Visible = xlVisibleSheet

Hide worksheet

Worksheets("Sheet1").visible = xlHiddenSheet

Hide a lot of worksheet

Worksheets("Sheet1").Visible = xlSheetVeryHidden

AutoMacro | Ultimate VBA Plugin | Click for a free trial!

Worksheet Level Events

eventsthey are triggers that can cause "event handlers" to be executed. For example, you can run code whenever a worksheet cell changes or a worksheet is activated.

Worksheet event procedures should be placed in a worksheet module:

VBA Spreadsheets - The Definitive Guide - Automating Excel (8)

There are numerous spreadsheet events. For a complete list, go toworksheet module, select "Worksheet" from the first dropdown menu. Then select an event handler from the second dropdown list to insert into the module.

VBA Spreadsheets - The Definitive Guide - Automating Excel (9)

Worksheet Activation Event

Worksheet activation events are executed every time the worksheet is opened.

private Sub Worksheet_Activate() Range("A1").SelectEnd Sub

This code will select cell A1 (resetting the view range to the top left corner of the spreadsheet) each time the spreadsheet is opened.

worksheet change event

Worksheet change events occur whenever a cell value in the worksheet changes. Read our tutorial onWorksheet Change Eventsfor more information

Worksheet Cheat Sheet

Below is a cheat sheet with common code examples for working with sheets in VBA

Cheatsheet for VBA worksheets

VBA worksheets cheat sheet

(Video) Advanced Excel . Automate your spreadsheets with formulas and easy-to-create macros

Descriptionexample of code
Referencing and activating sheets
tab nameSheets("Input").Activate
VBA codenameSheet1.Activate
indexingSheets(1).Enable
select sheet
select sheetSheets ("Input").Select
set to variabledim ws as worksheet
Set ws = active sheet
Name / RenameActiveSheet.Name = "NewName"
next sheetActiveSheet.Next.Activate
Loop through all sheetsdim ws as worksheet

For each ws in worksheets
msgbox ws.name
Next week

Iterate through the selected sheetsdim ws as worksheet

Para cada ws en ActiveWindow.SelectedSheets
MsgBox ws.Name
Next week

get active sheetMsgBox ActiveSheet.Nombre
add sheetSheets.Add
Add sheet and nameSheets.Add.Name = "New Sheet"
Add sheet with cell nameSheets.Add.Name = range("a3").value
Add sheets one at a timeSheets.Add After:=Sheets("Input")
Add and name the sheet afterSheets.Add(After:=Sheets("Input")).Name = "NewSheet"
Add sheet before and nameSheets.Add(Before:=Sheets("Input")).Name = "NewSheet"
Add sheet to end of workbookSheets.Add After:=Sheets(Sheets.Count)
Add sheet to top of workbookSheets.Add(Before:=Sheets(1)).Name = "FirstSheet"
add sheet to variabledim ws as worksheet
Set ws = Sheets.Add
copy worksheets
Move sheet to end of bookSheets("Sheet1").Move After:=Sheets(Sheets.Count)
To new workbookSheets("Sheet1").Copy
Selected sheets in a new workbookActiveWindow.SelectedSheets.Copiar
facing another sheetSheets("Sheet1"). Copy by:=Sheets("Sheet2")
Before the first sheetSheets("Sheet1"). Copy before:=Sheets(1)
after the last sheetSheets("Sheet1").Copiar nach:=Sheets(Sheets.Count)
copy and nameSheets("Sheet1").Copiar nach:=Sheets(Sheets.Count)
ActiveSheet.Name = "Last Sheet"
Copy from cell name and valueSheets("Sheet1").Copiar nach:=Sheets(Sheets.Count)
ActiveSheet.Name = Range("A1").Value
To another workbookSheets("Sheet1").Copy Before:=Books("Example.xlsm").Sheets(1)
Hide/show sheets
hide sheetSheets("Sheet1").visible = False
o
Sheets("Sheet1").visible = xlHiddenSheet
show sheetSheets("Sheet1").Visible = True
o
Sheets("Sheet1").Visible = xlVisibleSheet
very hidden bladeSheets(Sheet1).Visible = xlSheetVeryHidden
Delete or remove sheets
delete sheetSheets("Sheet1").Delete
Delete sheet (error handling)If there is an error, continue below
Sheets("Sheet1").Delete
In case of error Go to 0
Delete sheet (without notice)Application.DisplayAlerts = False
Sheets("Sheet1").Delete
Application.DisplayAlerts = True
clear sheetSheets("Sheet1").Cells.Delete
Clear only sheet contentSheets("Sheet1").Cells.ClearContents
Borrar Hoja UsadaRangoSheets("Sheet1").UsedRange.Clear
Protect or unprotect sheets
Unprotect (no password)Sheets("Sheet1"). check out
Unprotect (password)Sheets ("Sheet1"). Unprotect "password".
Protect (no password)Sheets("Sheet1").Protect
Protect (password)Sheets("Sheet1").Protect "Password"
Protect but allow access to VBASheets("Sheet1").Protect UserInterfaceOnly:=True
Unprotect all sheetsdim ws as worksheet

For each ws in worksheets
ws.Unprotect "Password"
Next week

Videos

1. Excel for Beginners: A Comprehensive Guide with ChatGPT #excelwithchatgpt | chatgptseexcel |
(kamka sikho)
2. Excel VBA Advanced Tutorial
(Learnit Training)
3. The Ultimate Guide to Copying Data using Excel VBA
(Excel Macro Mastery)
4. ChatGPT: Automating Excel with VBA like never before 🤯
(Coding Is Fun)
5. Automate your Daily Excel Work | Excel VBA Automation
(Buzz2day Tech)
6. How to Create & Use Excel Macros (Real world example)
(Leila Gharani)
Top Articles
Latest Posts
Article information

Author: Roderick King

Last Updated: 12/19/2022

Views: 6772

Rating: 4 / 5 (51 voted)

Reviews: 90% of readers found this page helpful

Author information

Name: Roderick King

Birthday: 1997-10-09

Address: 3782 Madge Knoll, East Dudley, MA 63913

Phone: +2521695290067

Job: Customer Sales Coordinator

Hobby: Gunsmithing, Embroidery, Parkour, Kitesurfing, Rock climbing, Sand art, Beekeeping

Introduction: My name is Roderick King, I am a cute, splendid, excited, perfect, gentle, funny, vivacious person who loves writing and wants to share my knowledge and understanding with you.