TrumpExcel Logo - Online Excel Tips & Tricks

Working with Worksheets using Excel VBA (Explained with Examples)

Picture of Sumit Bansal

Apart from cells and ranges , working with worksheets is another area you should know about using VBA efficiently in Excel.

Just like any object in VBA, worksheets have different properties and methods associated with them that you can use while automating your work with VBA in Excel.

In this tutorial, I will cover ‘Worksheets’ in detail and also show you some practical examples.

So let’s get started.

If you’re interested in learning VBA the easy way, check out my Online Excel VBA Training .

This Tutorial Covers:

Difference between Worksheets and Sheets in VBA

In VBA, you have two collections that can be a bit confusing at times.

In a workbook, you can have worksheets as well as chart sheets. The example below has three worksheets and one chart sheet.

Worksheets Object in Excel VBA - 1 Chart sheet

In Excel VBA:

  • The ‘Worksheets’ collection refers to the collection of all the worksheet objects in a workbook. In the above example, the Worksheets collection would consist of three worksheets.
  • The ‘Sheets’ collection would refer to all the worksheets as well as chart sheets in the workbook. In the above example, it would have four elements – 3 Worksheets + 1 Chart sheet.

If you have a workbook that only has worksheets and no chart sheets, then ‘Worksheets’ and ‘Sheets’ collection is the same.

But when you have one or more chart sheets, the ‘Sheets’ collection would be bigger than the ‘Worksheets’ collection

Sheets = Worksheets + Chart Sheets

Worksheets Collection Vs Sheets Collection

Now with this distinction, I recommend being as specific as possible when writing a VBA code.

So, if you have to refer to worksheets only, use the ‘Worksheets’ collection, and if you have to refer to all sheets (including chart sheets), the use the ‘Sheets’ collection.

In this tutorial, I will be using the ‘Worksheets’ collection only.

Referencing a Worksheet in VBA

There are many different ways you can use to refer to a worksheet in VBA.

Understanding how to refer to worksheets would help you write better code, especially when you’re using loops in your VBA code.

Using the Worksheet Name

The easiest way to refer to a worksheet is to use its name.

For example, suppose you have a workbook with three worksheets – Sheet 1, Sheet 2, Sheet 3.

And you want to activate Sheet 2.

The above code asks VBA to refer to Sheet2 in the Worksheets collection and activate it.

Since we are using the exact sheet name, you can also use the Sheets collection here. So the below code would also do that same thing.

Using the Index Number

While using the sheet name is an easy way to refer to a worksheet, sometimes, you may not know the exact name of the worksheet.

For example, if you’re using a VBA code to add a new worksheet to the workbook, and you don’t know how many worksheets are already there, you would not know the name of the new worksheet.

In this case, you can use the index number of the worksheets.

Suppose you have the following sheets in a workbook:

The below code would activate Sheet2:

Note that we have used index number 2 in Worksheets(2) . This would refer to the second object in the collection of the worksheets.

Now, what happens when you use 3 as the index number?

It will select Sheet3.

If you’re wondering why it selected Sheet3, as it’s clearly the fourth object.

This happens because a chart sheet is not a part of the worksheets collection.

Index Numbers in Worksheets Collection in VBA

On the contrary, if you’re using Sheets, Sheets(1) would refer to Sheets1, Sheets(2) would refer to Sheet2, Sheets(3) would refer to Chart1 and Sheets(4) would refer to Sheet3.

This technique of using index number is useful when you want to loop through all the worksheets in a workbook. You can count the number of worksheets and then loop through these using this count (we will see how to do this later in this tutorial).

Note: The index number goes from left to right. So if you shift Sheet2 to the left of Sheet1, then Worksheets(1) would refer to Sheet2.

Using the Worksheet Code Name

One of the drawbacks of using the sheet name (as we saw in the section above) is that a user can change it.

And if the sheet name has been changed, your code wouldn’t work until you change the name of the worksheet in the VBA code as well.

To tackle this problem, you can use the code name of the worksheet (instead of the regular name that we have been using so far).

A code name can be assigned in the VB Editor and doesn’t change when you change the name of the sheet from the worksheet area.

To give your worksheet a code name, follow the below steps:

  • Click the Developer tab.

Visual Basic button in the Developer tab

  • Click the View option in the menu and click on Project Window. This will make the Properties pane visible. If the Properties pane is already visible, skip this step.
  • Click on the sheet name in the project explorer that you want to rename.

CodeName of the Worksheet in VBA

The above steps would change the name of your Worksheet in the VBA backend.

In the Excel worksheet view, you can name the worksheet whatever you want, but in the backend, it will respond to both the names – the sheet name and the code name.

Worksheets Object in Excel VBA - Code name vs sheet name

In the above image, the sheet name is ‘SheetName’ and the code name is ‘CodeName’. Even if you change the sheet name on the worksheet, the code name still remains the same.

Now, you can use either the Worksheets collection to refer to the worksheet or use the codename.

For example, both the line will activate the worksheet.

The difference in these two is that if you change the name of the worksheet, the first one wouldn’t work. But the second line would continue to work even with the changed name. The second line (using the CodeName) is also shorter and easier to use.

Referring to a Worksheet in a Different Workbook

If you want to refer to a worksheet in a different workbook, that workbook needs to be open while the code runs, and you need to specify the name of the workbook and the worksheet that you want to refer to.

For example, if you have a workbook with the name Examples and you want to activate Sheet1 in the Example workbook, you need to use the below code:

Note that if the workbook has been saved, you need to use the file name along with the extension. If you’re not sure what name to use, take help from Project Explorer.

Worksheets Object in Excel VBA - file name in project explorer

In case the workbook has not been saved, you don’t need to use the file extension.

Adding a Worksheet

The below code would add a worksheet (as the first worksheet – i.e., as the leftmost sheet in the sheet tab).

It takes the default name Sheet2 (or any other number based on how many sheets are already there).

If you want a worksheet to be added before a specific worksheet (say Sheet2), then you can use the below code.

The above code tells VBA to add a sheet and then uses the ‘Before’ statement to specify the worksheet before which the new worksheet should to be inserted.

Similarly, you can also add a sheet after a worksheet (say Sheet2), using the below code:

If you want the new sheet to be added to the end of the sheets, you need to first know how many sheets are there. The following code first counts the number of sheets , and the adds the new sheet after the last sheet (to which we refer using the index number).

Deleting a Worksheet

The below code would delete the active sheet from the workbook.

The above code would show a warning prompt before deleting the worksheet .

Worksheets Object in Excel VBA - warning prompt

If you don’t want to see the warning prompt, use the below code:

When Application.DisplayAlerts is set to False, it will not show you the warning prompt. If you use it, remember to set it back to True at the end of the code.

Remember that you can’t undo this delete, so use the above code when you’re absolutely sure.

If you want to delete a specific sheet, you can do that using the following code:

You can also use the code name of the sheet to delete it.

Renaming the Worksheets

You can modify the name property of the Worksheet to change its name .

The following code will change the name of Sheet1 to ‘Summary’.

You can combine this with the adding sheet method to have a set of sheets with specific names.

For example, if you want to insert four sheets with the name 2018 Q1, 2018 Q2, 2018 Q3, and 2018 Q4, you can use the below code.

In the above code, we first count the number of sheets and then use a For Next loop to insert new sheets at the end. As the sheet is added, the code also renames it.

Assigning Worksheet Object to a Variable

When working with worksheets, you can assign a worksheet to an object variable, and then use the variable instead of the worksheet references.

For example, if you want to add a year prefix to all the worksheets, instead of counting the sheets and the running the loop that many numbers of times, you can use the object variable.

Here is the code that will add 2018 as a prefix to all the worksheet’s names.

The above code declares a variable Ws as the worksheet type (using the line ‘Dim Ws As Worksheet’).

Now, we don’t need to count the number of sheets to loop through these. Instead, we can use ‘For each Ws in Worksheets’ loop. This will allow us to go through all the sheets in the worksheets collection. It doesn’t matter whether there are 2 sheets or 20 sheets.

While the above code allows us to loop through all the sheets, you can also assign a specific sheet to a variable.

In the below code, we assign the variable Ws to Sheet2 and use it to access all of Sheet2’s properties.

Once you set a worksheet reference to an object variable (using the SET statement), that object can be used instead of the worksheet reference. This can be helpful when you have a long complicated code and you want to change the reference. Instead of making the change everywhere, you can simply make the change in the SET statement.

Note that the code declares the Ws object as the Worksheet type variable (using the line Dim Ws as Worksheet).

Hide Worksheets Using VBA (Hidden + Very Hidden)

Hiding and Unhiding worksheets in Excel is a straightforward task.

You can hide a worksheet and the user would not see it when he/she opens the workbook. However, they can easily unhide the worksheet by right-clicking on any sheet tab.

Worksheets Object in Excel VBA - unhide right-click macro

But what if you don’t want them to be able to unhide the worksheet(s).

You can do this using VBA.

The code below would hide all the worksheets in the workbook (except the active sheet), such that you can not unhide it by right-clicking on the sheet name.

In the above code, the Ws.Visible property is changed to xlSheetVeryHidden .

  • When the Visible property is set to xlSheetVisible, the sheet is visible in the worksheet area (as worksheet tabs).
  • When the Visible property is set to xlSheetHidden, the sheet is hidden but the user can unhide it by right-clicking on any sheet tab.
  • When the Visible property is set to xlSheetVeryHidden, the sheet is hidden and cannot be unhidden from worksheet area. You need to use a VBA code or the properties window to unhide it.

If you want to simply hide sheets, that can be unhidden easily, use the below code:

The below code would unhide all the worksheets (both hidden and very hidden).

Hide Sheets Based on the Text in it

Suppose you have multiple sheets with the names of different departments or years, and you want to hide all the sheets except the ones that have the year 2018 in them.

You can do this using a VBA INSTR function .

The code below would hide all the sheets except the ones with the text 2018 in them.

In the above code, the INSTR function returns the position of the character where it finds the matching string. If it doesn’t find the matching string, it returns 0.

The above code checks whether the name has the text 2018 in it. If it does, nothing happens, else the worksheet is hidden.

You can take this a step further by having the text in a cell and using that cell in the code.

This will allow you to have a value in the cell, and then when you run the macro, all the sheets, except the one with the matching text in it, will remain visible (along with the sheets where you’re entering the value in the cell).

Sorting the Worksheets in an Alphabetical Order

Using VBA, you can quickly sort the worksheets based on their names.

For example, if you have a workbook that has sheets for different departments or years, then you can use the below code to quickly sort these sheets in an ascending order.

Note that this code works well with text names and in most of the cases with years and numbers too. But it can give you the wrong results in case you have the sheet names as 1,2,11. It will sort and give you the sequence 1, 11, 2. This is because it does the comparison as text and considers 2 bigger than 11.

Protect/Unprotect All the Sheets at One Go

If you have a lot of worksheets in a workbook and you want to protect all the sheets, you can use the VBA code below.

It allows you to specify the password within the code. You will need this password to unprotect the worksheet.

The following code would unprotect all the sheets in one go.

Creating a Table of Contents of All Worksheets (with Hyperlinks)

If you have a set of worksheets in the workbook and you want to quickly insert a summary sheet which has the links to all the sheets, you can use the below code.

The above code inserts a new worksheet and names it Index.

It then loops through all the worksheets and creates a hyperlink for all the worksheets in the Index sheet.

Where to Put the VBA Code

Wondering where the VBA code goes in your Excel workbook?

Excel has a VBA backend called the VBA editor. You need to copy and paste the code into the VB Editor module code window.

Here are the steps to do this:

IF Then Else in Excel VBA - Developer Tab in ribbon

  • In the Project Explorer pane in the VB Editor, right-click on any object for the workbook in which you want to insert the code. If you don’t see the Project Explorer go to the View tab and click on Project Explorer.

VBA Loops - inserting module

You May Also Like the Following Excel VBA Tutorials:

  • Working with Workbooks using VBA .
  • Using IF Then Else Statements in VBA .
  • For Next Loop in VBA .
  • Creating a User-Defined Function in Excel .
  • How to Record a Macro in Excel .
  • How to Run a Macro in Excel .
  • Excel VBA Events – An Easy (and Complete) Guide .
  • How to Create an Add-in in Excel .
  • How to Save and Reuse Macro using Excel Personal Macro Workbook .
  • Using Active Cell in VBA in Excel (Examples)
  • How to Open Excel Files Using VBA (Examples)

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Picture of Sumit Bansal

3 thoughts on “Working with Worksheets using Excel VBA (Explained with Examples)”

How do you refer to the sheet code name of a different workbook? in particular, I would like to set a work book as a variable (set wb = workbook) and then refer to this work book’s sheets using their code names in case individuals decide to change the sheet name.

Hi I’ve created just one worksheet where a number of the columns have drop down lists. I wanted to able to have multiple selection non repetitive on these so I adapted the VBA code as indicated in one of your earlier tutorials. This only worked for a limited number of rows/ columns though and I need it to apply to the whole worksheet. Is there a code that satisfies this??? Is it the code above??? Where do I insert it in existing code?? Your help would be really appreciated

Making the same change to multiple worksheets or say i want to have one master worksheet and the changes i make in master sheet will automatically implement in all other (NOTE : without selecting all worksheets using Ctrl Key)

Leave a Comment Cancel reply

BEST EXCEL TUTORIALS

Best Excel Shortcuts

Conditional Formatting

Excel Skills

Creating a Pivot Table

Excel Tables

INDEX- MATCH Combo

Creating a Drop Down List

Recording a Macro

© TrumpExcel.com – Free Online Excel Training

DMCA.com Protection Status

Privacy Policy  | Sitemap

Twitter | Facebook | YouTube | Pinterest | Linkedin

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Free Excel Tips EBook Sumit Bansal

MrExcel Message Board

  • Search forums
  • Board Rules

Follow along with the video below to see how to install our site as a web app on your home screen.

Note: This feature may not be available in some browsers.

  • If you would like to post, please check out the MrExcel Message Board FAQ and register here . If you forgot your password, you can reset your password .
  • Question Forums
  • Excel Questions

Passing a Worksheet into a Sub()

  • Thread starter Chrisjd2
  • Start date Feb 16, 2017

Board Regular

  • Feb 16, 2017

Excel Facts

RoryA

MrExcel MVP, Moderator

Re: Passing a Worksheet into a Sub(), HELP! You don't really need the additional variable, just replace this: Code: SHT = wsOld Call LoadDataString(SHT) with this: Code: Call LoadDataString(wsOld)  

Re: Passing a Worksheet into a Sub(), HELP! I want to be able to run the macro, with applying it to different sheets instead of re-writing the macro each time. I.e Code: Sub RunProgram SHT =wsOld Call LoadDataString(SHT) SHT =wsNew Call LoadDataString(SHT) End sub  

Joe4

MrExcel MVP, Junior Admin

Re: Passing a Worksheet into a Sub(), HELP! Since SHT is a worksheet object, shouldn't it be: Code: [COLOR=#ff0000][B]Set[/B][/COLOR] SHT = wsOld Though Rory makes a good point. There isn't really any reason to have this extra variable. It seems rather unnecessary. Just use: Code: Call LoadDataString(wsOld) Call LoadDataString(wsNew)  

Joe4 said: Since SHT is a worksheet object, shouldn't it be: Code: [COLOR=#ff0000][B]Set[/B][/COLOR] SHT = wsOld Though Rory makes a good point. There isn't really any reason to have this extra variable. It seems rather unnecessary. Just use: Code: Call LoadDataString(wsOld) Call LoadDataString(wsNew) Click to expand...

Re: Passing a Worksheet into a Sub(), HELP! It will simply work as it is. As far as the LoadDataString routine is concerned, the variable passed in is called SHT. That does not require you to pass in a variable of the same name.  

Re: Passing a Worksheet into a Sub(), HELP! Nice one thanks, It works well now. How could I do the same with the Getopenfilename? So the Title is different etc... Code: Sub CallRoutine() Set wsOld = Worksheets("Old Variant CADEX") Set wsNew = Worksheets("New Variant CADEX") Dim Title As String Call LoadData(wsOld, "Text Files (*.txt),*.txt", , "Old Data Drop") End Sub Sub LoadCadex(Worksheet, Title As String) Dim Myfile As String Dim myline As String Dim Fileline() As String Myfile = Application.GetOpenFilename() If Myfile = "" Then GoTo LINE2 End If Counter = 1  

Re: Passing a Worksheet into a Sub(), HELP! Just pass the argument along: Code: Myfile = Application.GetOpenFilename(Title:=Title) You can probably see why it's better to use a variable name that isn't also the name of a parameter.  

Re: Passing a Worksheet into a Sub(), HELP! I see. Code: Sub CallRoutine() Set wsOld = Worksheets("Old Variant CADEX") Set wsNewLocations = Worksheets("New Cadex Locations") Set wsOldLocations = Worksheets("Old Cadex Locations") Dim Title As String myfile = application.GetOpenFilename("Text Files (*.txt),*.txt", , "Old Data") Call LoadData(wsOld, myfile) End Sub Private Sub LoadData(Worksheet, myfile As String) Dim myfile As String Dim myline As String Dim Fileline() As String 'myfile = Application.GETOPENFILENAME If myfile = "" Then GoTo LINE2 End If I get an error, Byref argument type mismatch.. P.s I am new to VBA and self taught lol  

Re: Passing a Worksheet into a Sub(), HELP! Don't know if it is what is causing your problem, but you should never use reserved words (the names of functions, objects, properties, methods, etc) as the name of your Procedures, Functions, or Variables. It can cause ambiguity, unexpected results, and errors. You have a variable named Worksheet, which is a reserved word: Code: Private Sub LoadData([COLOR=#ff0000]Worksheet[/COLOR], myfile As String) Use something different, like "myWorksheet" or "ws".  

Similar threads

  • Mar 2, 2023

ChrisGudrich

  • ChrisGudrich
  • Sep 5, 2022
  • May 31, 2023
  • HeinrichPaul
  • Sep 17, 2023
  • May 14, 2023

Forum statistics

Share this page.

worksheet pass vba

We've detected that you are using an adblocker.

Which adblocker are you using.

AdBlock

Disable AdBlock

worksheet pass vba

Disable AdBlock Plus

worksheet pass vba

Disable uBlock Origin

worksheet pass vba

Disable uBlock

worksheet pass vba

Excel Macro Mastery

The Complete Guide To The VBA Worksheet

by Paul Kelly | | Excel Specific , VBA Language | 56 comments

  “The visionary starts with a clean sheet of paper, and re-imagines the world” – Malcolm Gladwell

  This post provides a complete guide to using the Excel VBA Worksheet in Excel VBA. If you want to know how to do something quickly then check out the quick guide to the VBA Worksheet below.

If you are new to VBA then this post is a great place to start. I like to break things down into simple terms and explain them in plain English without the jargon.

You can read through the post from start to finish as it is written in a logical order. If you prefer, you can use the table of contents below and go directly to the topic of your choice.

   

  • 1 A Quick Guide to the VBA Worksheet
  • 2 Introduction
  • 3.1 Hide Worksheet
  • 3.2 Protect Worksheet
  • 3.3 Subscript Out of Range
  • 4 Using the Index  to Access the Worksheet
  • 5.1 Code Name in other Workbooks
  • 5.2 Code Name Summary
  • 6 The Active Sheet
  • 7 Declaring a Worksheet Object
  • 8 Accessing the Worksheet in a Nutshell
  • 9 Add Worksheet
  • 10 Delete Worksheet
  • 11 Loop Through the Worksheets
  • 12 Using the Sheets Collection
  • 13 Conclusion
  • 14 What’s Next?

A Quick Guide to the VBA Worksheet

The following table gives a quick run down to the different worksheet methods.

Note: I use Worksheets in the table below without specifying the workbook i.e. Worksheets rather than ThisWorkbook.Worksheets , wk.Worksheets etc. This is to make the examples clear and easy to read. You should always specify the workbook when using Worksheets . Otherwise the active workbook will be used by default.

Introduction

The three most important elements of VBA are the Workbook , the Worksheet and Cells . Of all the code your write, 90% will involve one or all of them.

The most common use of the worksheet in VBA is for accessing its cells. You may use it to protect, hide, add, move or copy a worksheet. However, you will mainly use it to perform some action on one or more cells on the worksheet.

    Using Worksheets is more straightforward than using workbooks. With workbooks you may need to open them, find which folder they are in, check if they are in use and so on. With a worksheet, it either exists in the workbook or it doesn’t.

Accessing the Worksheet

In VBA, each workbook has a collection of worksheets. There is an entry in this collection for each worksheet in the workbook. This collection is simply called Worksheets and is used in a very similar way to the Workbooks collection. To get access to a worksheet all you have to do is supply the name.

    The code below writes “Hello World” in Cell A1 of Sheet1, Sheet2 and Sheet3 of the current workbook.

    The Worksheets collection is always belong to a workbook. If we don’t specify the workbook then the active workbook is used by default.

Hide Worksheet

The following examples show how to hide and unhide a worksheet

    If you want to prevent a user accessing the worksheet, you can make it “very hidden”. This means it can only be made visible by the code.

Protect Worksheet

Another example of using the worksheet is when you want to protect it

Subscript Out of Range

When you use Worksheets you may get the error:

Run-time Error 9 Subscript out of Range

VBA Subscript out of Range

    This means you tried to access a worksheet that doesn’t exist. This may happen for the following reasons

  • The worksheet name given to Worksheets is spelled incorrectly.
  • The name of the worksheet has changed.
  • The worksheet was deleted.
  • The index was to large e.g. You used Worksheets(5) but there are only four worksheets
  • The wrong workbook is being used e.g. Workbooks( “book1.xlsx” ).Worksheets(“Sheet1”) instead of Workbooks( “book3.xlsx” ).Worksheets(“Sheet1”) .

    If you still have issues then use one of the loops from Loop Through The Worksheets section to print the names of all worksheets the collection.

Using the Index  to Access the Worksheet

So far we have been using the sheet name to access the sheet. The index refers to the sheet tab position in the workbook. As the position can easily be changed by the user it is not a good idea to use this.

    The following code shows examples of using the index

    In the example above, I used Debug.Print to print to the Immediate Window. To view this window select View->Immediate Window(or Ctrl G)

ImmediateWindow

Using the Code Name of a Worksheet

The best method of accessing the worksheet is using the code name. Each worksheet has a sheet name and a code name. The sheet name is the name that appears in the worksheet tab in Excel.

Changing the sheet name does not change the code name meaning that referencing a sheet by the code name is a good idea.

    If you look in the VBE property window you will see both names. In the image you can see that the code name is the name outside the parenthesis and the sheet name is in the parenthesis.

code name worksheet

    If your code refers to the code name then the user can change the name of the sheet and it will not affect your code. In the example below we reference the worksheet directly using the code name.

    This makes the code easy to read and safe from the user changing the sheet name.

Code Name in other Workbooks

There is one drawback to using the code name. It can only refer to worksheets in the workbook that contains the code i.e. ThisWorkbook.

    However, we can use a simple function to find the code name of a worksheet in a different workbook.

    Using the above code means that if the user changes the name of the worksheet then your code will not be affected.

There is another way of getting the sheet name of an external workbook using the code name. You can use the VBProject element of that Workbook.

    You can see how to do this in the example below. I have included this for completeness only and I would recommend using the method in the previous example rather than this one.

Code Name Summary

The following is a quick summary of using the Code Name

  • The code name of the worksheet can be used directly in the code e.g. Sheet1.Range
  • The code name will still work if the worksheet name is changed.
  • The code name can only be used for worksheets in the same workbook as the code.
  • Anywhere you see ThisWorkbook.Worksheets(“sheetname”) you can replace it with the code name of the worksheet.
  • You can use the SheetFromCodeName function from above to get the code name of worksheets in other workbooks.

The Active Sheet

The ActiveSheet  object refers to the worksheet that is currently active. You should only use ActiveSheet if you have a specific need to refer to the worksheet that is active.

Otherwise you should specify the worksheet you are using.

If you use a worksheet method like Range and don’t mention the worksheet, it will use the active worksheet by default.

Declaring a Worksheet Object

Declaring a worksheet object is useful for making your code neater and easier to read.

The next example shows code for updating ranges of cells. The first Sub does not declare a worksheet object. The second sub declares a worksheet object and the code is therefore much clearer.

    You could also use the With keyword with the worksheet object as the next example shows.

Accessing the Worksheet in a Nutshell

With all the different ways to access a worksheet, you may be feeling overwhelmed or confused. So in this section, I am going to break it down into simple terms

    1. If you want to use whichever worksheet is currently active then use ActiveSheet .

    2. If the worksheet is in the same workbook as the code then use the Code Name .

    3. If the worksheet is in a different workbook then first get workbook and then get the worksheet.

    If you want to protect against the user changing the sheet name then use the SheetFromCodeName function from the Code Name section.

Add Worksheet

The examples in this section show you how to add a new worksheet to a workbook. If you do not supply any arguments to the Add function then the new worksheet will be placed before the active worksheet.

When you add a Worksheet, it is created with a default name like “Sheet4”. If you want to change the name then you can easily do this using the Name property.

    The following example adds a new worksheet and changes the name to “Accounts”. If a worksheet with the name “Accounts” already exists then you will get an error.

    In the previous example, you are adding worksheets in relation to the active worksheet. You can also specify the exact position to place the worksheet.

    To do this you need to specify which worksheet the new one should be inserted before or after. The following code shows you how to do this.

Delete Worksheet

To delete a worksheet you simply call the Delete member.

    Excel will display a warning message when you delete a worksheet. If you want to hide this message you can use the code below

    There are two issues to watch out for when it comes to deleting worksheets.

If you try to access the worksheet after deleting it you will get the “Subscript out of Range” error we saw in the Accessing the Worksheet section.

    The second issue is when you assign a worksheet variable. If you try to use this variable after the worksheet is deleted then you will get an Automation error like this

Run-Time error -21147221080 (800401a8′) Automation Error

If you are using the Code Name of the worksheet rather than a variable, then this will cause Excel to crash rather than give the automation error.

    The following example shows how an automation errors occurs

If you assign the Worksheet variable to a valid worksheet it will work fine

Loop Through the Worksheets

The Worksheets member of Workbooks is a collection of worksheets belonging to a workbook. You can go through each sheet in the worksheets collection using a For Each Loop or a For  Loop.

    The following example uses a For Each loop.

    The next example uses the standard For loop

    You have seen how to access all open workbooks and how to access all worksheets in ThisWorkbook. Lets take it one step further. Lets access all worksheets in all open workbooks.

Note: If you use code like this to write to worksheets then back everything up first as you could end up writing the incorrect data to all the sheets.

Using the Sheets Collection

The workbook has another collection similar to Worksheets called Sheets . This causes confusion at times among users. To explain this first you need to know about a sheet type that is a chart.

    It is possible in Excel to have a sheet that is a chart. To do this

  • Create a chart on any sheet.
  • Right click on the chart and select Move.
  • Select the first option which is “New Sheet” and click Ok.

    Now you have a workbook with sheets of type worksheet and one of type chart.

  • The Worksheets collection refers to all worksheets in a workbook. It does not include sheets of type chart.
  • The Sheets collection refers to all sheets belonging to a workbook including sheets of type chart.

    There are two code examples below. The first goes through all the Sheets in a workbook and prints the name of the sheet and type of sheet it is. The second example does the same with the Worksheets collection.

    To try out these examples you should add a Chart sheet to your workbook first so you will see the difference.

    If do not have chart sheets then using the Sheets collection is the same as using the Worksheets collection.

This concludes the post on the VBA Worksheet. I hope you found it useful.

The three most important elements of Excel VBA are Workbooks , Worksheets and Ranges and Cells . These elements will be used in almost everything you do.  Understanding them will make you life much easier and make learning VBA much simpler.

What’s Next?

Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial .

Related Training: Get full access to the Excel VBA training webinars and all the tutorials .

( NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)

56 Comments

Peter

Thank you for the excellent information for a VBA novice of some 10 years

Paul Kelly

You’re welcome Peter.

George Cook

I’ve just come across your blog and it looks most informative. I’ve not had time to read through it yet and the answer to my question may be in there somewhere but in the meantime Is there a shorter way (in a macro) to write If Cell.Value = “A” Or Cell.Value = “B” Or Cell.Value = “C” Then Cell.Value = 2

Many thanks

You can use the Select Case statement to do it

Thanks for that Paul.

In the time between my query and your answer I did find a way except a bit longer than what you have suggested.

This is what I found

For Each oCell In Range(“B11:M32”) Select Case oCell.Value Case Is = “A”, “B”, “C”: oCell.Value = 2 Case Is = “D”, “E”, “F”: oCell.Value = 3 Case Is = “G”, “H”, “I”: oCell.Value = 4 Case Is = “J”, “K”, “L”: oCell.Value = 5 Case Is = “M”, “N”, “O”: oCell.Value = 6 Case Is = “P”, “Q”, “R”, “S”: oCell.Value = 7 Case Is = “T”, “U”, “V”: oCell.Value = 8 Case Is = “W”, “X”, “Y”, “Z”: oCell.Value = 9 End Select Next

Anyway thanks again. Much appreciated.

SHAIBUL HOQUE MAZUMDER

How to change one or moreTabs name or worksheets name in upper Case by vba? my worksheets has 100 nos

mike denley

I use the code name to identify the different types of worksheets that are being created/used; allowing users to rename the worksheet as they see fit. From your experience what is the best way to set the code name ?

I normally set it manually in the property windows. It can be done using the code but normally this is not necessary.

harsha547

Thanks for sharing !!

Jorge C

Hi Paul, I am really really really thankful for your very informative, didactic and amusing posts. I have learned a lot since I began to read and rewrite your code, while adding slight changes and experimenting. I just have a comment, I found that into the Sub AllSheetNames(), the second “next” asks for an “i” variable where the proper one should be “wrk” Thank you again for everything and keep making live videos.

Thanks Jorge, I have update the code. Glad you enjoy the website posts.

Afzal khan

Thanks for your great effort Paul Kelly. It’s very helpful to developing towards VBA programmer.

But one question, you have not shown how the “codename” for the worksheet is made. Only “codename” is displayed in the property screen shot.

Thanks for sharing knowledge…. I am glad i found this site.

Ya i got it, its already mentioned there. Sorry i missed it.

No problem Afzal.

Curzio

Hallo Paul, your whole site is the mandatory start lane and pit stop for every vba writer. I get confused by example “Code Name in other Workbooks”: I miss somewhere something like

Dim iWB As Workbook For Each iWB In Application.Workbooks ‘Loop thru Worksheets and check condition Next

Thank again and kind regards

Chris K.

You stated that codenames can only be used in “Thisworkbook”, but in my testing the module that I’m running writes to two workbooks that I have opened at the same if the other workbook is active. Here’s what I did. I declared a public codename in workbook1 and set it in ThisWorkbook object using Auto_Open. (I’ve tested setting it in a module as well and I get the same results) The module which contains my code starts with “With ThisWorkbook” before calling the public variable. As a test, I renamed a sheet from each workbook with the same codename. I would expect that my code would only run in workbook1 as the public variable is written, set and called in workbook1; on top of that I declared “With Thisworkbook” at the start of my module.

Do you know how I can prevent the module from running in the wrong workbook? I know I can simply rename the codename in workbook1 to something highly unlikely, but for theoretical purposes I’d like not to do that.

Thanks, Chris

Upon further testing regardless of the other workbooks codename if it was active my module would write to it. I changed the codenames to differ and I had the same results; even if the other workbook was an xlsx file the module still wrote to it. For now, I’m going with a workaround to state in my code Thisworkbook.active. This prevented writing to the other workbook even if I clicked on it during runtime.

Okay, so what I found out is that in my module I have lines of code such as range(“A2”).value, etc. As a result, excel or vba reads it like ActiveSheet.Range(“A2”).value. I had thought that starting the code with “With Thisworkbook” I would have avoided the code from writing or reading other workbooks.

What I did to resolve this is instead of range(“A2”).value and I used VBsht.Range(“A2”).value with VBsht being my public variable or object.

DangBui

I am new to VBA and am learning from beginning. Thank you for your useful sharing!

You’re welcome.

Guity

OMG, I have been looking for someone to teach me macros as how to write macros. I found this site, it seems to me a new world has opened its door to me. It is unbelievable!!!

Glad you like it so much:-)

Nick Jensen

Hello Paul,

Great stuff you have here. I really appreciate your thoroughness and clarity. For a nitpicker like me, it is great to have clear explanations for many things you write in code.

In this article, there is a section called “Code Name in other Workbooks” which I have a question about. You write, “There is another way of getting the sheet name of an external workbook using the code name. You can use the VBProject element of that Workbook… I would recommend using the method in the previous example rather than this one”. Could you explain why you recommend the looping technique over using VBProject elements?

Using the VBProject requires that the user has checked the “Trust Access” box in the security settings. Which is fine if you are just using it yourself.

Loki

I don’t know how I stumbled upon your site, but I am confident that this will hands down be the greatest thing in my VBA journey.

I believe in the sub UseSheet of ‘Code Name in other Workbooks’ section, the argument ThisWorkbook must be replaced with the workbook you are trying to access the sheets of, like Workbooks(“Book2.xlsx”) .

Thanks Loki. ThisWorkbook refers to the current workbook i.e. the workbook containing the VBA code.

Ashish

Dear Paul, Thanks for this informative article. I tried to set two worksheetsheet variables to access two sheets of a new workbook but VBA is throwing error on assigning second sheet to second variable. Below is the code

Sub test27() Dim temp_wkb As Workbook Dim temp_sheet1, temp_sheet2 As Sheets Set temp_wkb = Workbooks.Add Set temp_sheet1 = temp_wkb.Sheets(1) Set temp_sheet2 = temp_wkb.Sheets(2) End Sub

I get Error 13 type mismatch on “Set temp_sheet2 = temp_wkb.Sheets(2)”

Could you please guide as to what am I doing wrong here?

Regards Ashish

temp_sheet2 As Sheets

This code should be “As Sheet” or better still “As Worksheet”

Dušan

Why this Sub procedure doesn’t work for me. Please answer. Public Sub UseCodeName2()

‘ Using the code name of the worksheet Debug.Print CodeName.Name CodeName.Range(“A1”) = 45 CodeName.Visible = True

Goodby ! Dušan

You must have a worksheet with the code name set to CodeName

Jérôme Turmel

Paul, I read all the articles on your site and I would like to show you all my gratitude for your information sharing. Having a simple structure with multiple examples, easy to copy and to understand, makes the student’s job very simple. You are an excellent teacher.

Thank you for the nice work.

Thanks Jérôme

ludovic

hi, is it possible to refer with a variable to the sheets codename;

dim sht as worksheet, strCName as string strCName = “cnTestCodeName” set sht = sheets(strCName) ‘this does not fly for me when i want to refer to the sheets CodeName

in general impressive tutorials / on udemy it could had gone some deeper for me, but good.

Why would you want to? When you use the codename you are directly accessing the worksheet object. You can do this: Dim sht As Worksheet Set sht = sheets(cnSheet1.Name)

But it’s the same as Set sht = cnSheet1

Thanks for reply, will try to explain my ‘idea’. I have a hidden ‘classSheet’ that holds a range of predefined zoom values for a set of sheets in the workbook. A function setting all those predefined zooms to the ‘default’ by looping true all sheets referred to by codename. Now if I use the sheetname instead the user could have changed this sheetname. For the codename I wanted to check if the sheet still exists (Excel will crash referring to a sheets codename that does not exist) and loop true the collection. As a result, of this approach, I thought to be pretty sure all goes as intended to. It is probably not a capital case but I thought I miss out on something easy once you know. Thanks.

You can use the SheetFromCodeName() function in this section of the post to get the worksheet object from the codename text.

sorry for late reply, i only have time in my weekends. thanks for the tip, prefect workable approach. Ludovic

SHIN DONGJUN

If I found you earlier, I could dig treasure in Excel. Until now, I just have used simple function of it. Anyway, I keep turning on your tutorial everyday. Again, Thanks for your great lecture.

Thanks Shin.

Rajeev Shukla

Sir, I have a workbook in which there is a worksheet in which we had used some formulas. I want to make a duplicate sheet by a command button with only values (paste special). Please Guide me

Lutta

Hi Paul and all,

Thanks for the great resources herein.

I would like to copy the values in cell C1 from a number of worksheets and paste in range C2 : C13 of my summary worksheet in the same workbook, and subsequently in the empty cells that follow. But instead of getting a set of 12 unique values, I get a long list with the same values from one worksheet.

Here is my code: Sub Tabulates()

Dim erow As Long

For i = 1 To Worksheets.Count If Worksheets(i).Name “Data” Then

erow = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0).row + 1

Worksheets(i).Range(“C1”).Copy Sheet1.Range((“C2:C13”) & erow).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True Application.CutCopyMode = False

End If Next End Sub

Kindly assist. Thank you.

Rupa

Can you please help me to solve this problem? Eight number line shows error. Sub Macro1() Dim c, m, n, f As Integer c = 4 ‘Total room number m = 1 For f = 1 To c If (f <= c – 3) Then Worksheets("Bedroom" & m).Activate Worksheets("Load Factor").Cells(6, f + 2).Formula = "="Bedroom"&m!B2" m = m + 1 End If Next f End Sub

What is the error?

Abdul Majeed

Dear Sir, I am looking for a vba code to find whether a worksheet is “Protected” or “Unprotected”. Thanks & Kind Regards Majeed

Marc Sanschagrin

Hi, Great resources, specially for an absolute beginner like me.

I almost know nothing about Excel, but I’m really motivated to learn.

I’m on a project to collect software inventory from a list of computers, for this part I have managed to make individual inventory and merge the different worksheets in a single workbook.

In my workbook I also have the content of the base image, which I use as a reference to remove the lines that are in this specific sheet from the other sheets, that’s working fine.

But as it sometimes happens That on certain computers, there is no other software than the base image I get empty inventory sheets after the cleanup and that’s causing me trouble as sometimes my account doesn’t have access to certain computers and instead of crashing my entire tool, I issue an empty sheet and that’s my way to find computers on which I have no access.

But after cleaning up my sheets I can’t figure out which are empty because the lack of access or the absence of software added to the base image.

So, here’s where my problem begins, I just can’t figure out how to instead of deleting those lines, just change the background colors for these rows.

So here is my actual code and any help would be really appreciated.

******************************** Option Explicit

Sub NettoyeurDeFeuilles() Application.ScreenUpdating = False

Dim ws As Worksheet Dim startRow As Integer

startRow = 1 Dim row As Integer

row = startRow

Dim bRow As Integer

For Each ws In ThisWorkbook.Worksheets With ws ‘Ne pas chercher dans la feuille Image_De_Base_W7! If ws.Name = “Image_De_Base_W7” Then GoTo myNext

Do While (Worksheets(ws.Name).Range(“A” & row).Value “”)

Dim aVal As String

aVal = Worksheets(ws.Name).Range(“A” & row).Value

bRow = startRow

Do While (Worksheets(“Image_De_Base_W7”).Range(“A” & bRow).Value “”)

Dim aVal2 As String

aVal2 = Worksheets(“Image_De_Base_W7”).Range(“A” & bRow).Value

If (aVal = aVal2) Then

Worksheets(ws.Name).Rows(row).Delete ‘ Une entrée à supprimé à été trouvé row = row – row

‘ Worksheets(ws.Name).Rows(row).Cells.Interior.ColorIndex = 3

bRow = bRow + 1

row = row + 1 Loop

myNext: End With

***********************

Anil

Suppose you have a 7 sheets. I want to protect sheet 2,4,6 by immediate window vba code. how?

David

Hello Paul Kelly, thanks for the amazing resources on your site, I have been using them a lot and it helped me learn faster and save a lot of time. I have a suggestion for you. I have modified your code to use worksheets froom codenames on another workbook

Here is the code:

‘ https://excelmacromastery.com/ Public Function UseSheetCodeName(CodeName As String, Wbk As Workbook) As Worksheet

Dim sh As Worksheet ‘ Get the worksheet using the codename Set sh = SheetFromCodeName(CodeName, Wbk) ‘ Use the worksheet Debug.Print sh.Name

End Function

‘ This function gets the worksheet object from the Code Name Private Function SheetFromCodeName(Name As String, bk As Workbook) As Worksheet

Dim sh As Worksheet For Each sh In bk.Worksheets If sh.CodeName = Name Then Set SheetFromCodeName = sh Exit For End If Next sh

James

Hi Paul. I’m new to VBA and am stuck on the ‘Code Name in Other Workbooks’ section. I’ve read the comments and can’t work it out. When I run the code I see the following error:

Run-time error ’91’ Object variable or With block variable not set.

Debug references the line:

Debug.Print sh.Name

Am I supposed to have created a separate workbook and worksheet before running the macro?

Hi Paul, After returning to the code the morning after, I’m pleased to say perseverance has won the day. I declared the ‘name’ variable incorrectly in SheetFromCodeName function line. I declared it as a plural ‘names’. I don’t yet know how to step through the code to debug this sort of thing but will get there. Thanks again.

Glad you got it sorted James.

M. Qadeer

Hi Sir, I’m new to VBA and am stuck on the Update or delete function. I’ve read the comments and can’t work it out. When I run the code I see the following error:

Run-time error ‘1004’: “Unable to get the Match property of the WorksheetFunction class” Show.

Click on Debug,

Selected_Row = Application.WorksheetFunction.Match(CLng(Me.TxtSalPri.Value), sh.Range(“A:A”), 0)

that line is highlighted with yellow

JERRY JONES

Code name from other workbooks: I’m a tad lost but see the huge value of understanding this section. 1) When I run this code I get an error: Object varialbe or With block variable not set. I assume this is the “Codename” variable in the function call? 2) Overall, I think the purpose of this is to find all code names in the other workbook, but it passes “ThisWorkbook” to the function and it seems it should be passing the other workbook name. 3) Where does the other workbook name come in to find the sh code name(s)? I assume this is just trying to confirm that a sh code name exists in the other workbook but I can’t resolve the error to continue digesting how the code is working.

It takes a workbook object as the parameter and not the workbook name. I used ThisWorkbook as an example here as it is easy to demonstrate the code. You can replace it with any workbook object.

Luis G Polanco

Hi, Good evening Paul, while going over my correspondence I found your email, which to my surprise have the link to this Worderfull Complete Guide.. Immediately I downloaded as a PDF and found it covers very Important information which I was searching days ago on the Internet.. You are Great.. really Thank You for Sharing..

If God spears life I will try sending a Contribution, unfortunately I can promise much, but a couple of hundreds always help.. Best Regards..

Glad you found it useful.

Penny

Hi Mr Kelly, Thank you for all the great comments. I am in search of a way to split worksheets into separate workbooks and name the workbooks the same name as the worksheet. These are preformatted reports I’m pulling out of a financial system and there will be 200 worksheets per workbook to start with. I want to end up with 200 separate files. Do you do any coding that relates to splitting a workbook? What I have is naming the workbooks Book1, Book2, Book3 right now and I need the sheet name to be the name of the workbook.

Sub Splitbook()

MyPath = ThisWorkbook.Path

For Each sht In ThisWorkbook.Worksheets

ActiveSheet.Cells.Copy

ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues

ActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats

ActiveWorkbook.SaveAs _

Filename = MyPath & “\” & ActiveSheet.Name & “.xls”

ActiveWorkbook.Close savechanges:=False

Submit a Comment Cancel reply

Your email address will not be published. Required fields are marked *

worksheet pass vba

You are not logged in

You are not currently logged in.

TeachExcel

  • Excel Courses
  • Free Excel Courses
  • Excel Video Tutorials
  • Excel Keyboard Shortcuts

Premium Excel Course Now Available!

Build professional - unbreakable - forms in excel, 45 tutorials - 5+ hours - downloadable excel files, pass arguments to a macro called from a button or sheet in excel, black friday sale (65%-80% off).

Excel Courses Online Video Lessons Excel Guides

VBA Course (80% Off)

Forms Course (65% Off)

Email Course (65% Off)

How to pass arguments and values to macros called from worksheets, buttons, and anything else from the Excel interface.

This is different than passing a value from one macro to another .

Create the Macro to Accept the Value

Pass a text value to the macro, pass a numeric value to the macro, pass multiple values to the macro, pass the value of a cell to the macro, pass the value of a name to the macro, call the macro from a button or other object/shape, alternate format to reference the macros.

To allow a macro to accept a value, you simply type a name for that value between the parentheses at the top of the macro.

Here is our sample macro:

This macro accepts a value in the message variable and then outputs it into a message box pop-up.

a15a2ac387999ddf51854fc2841aa2ce.jpg

When you go to run this macro from the worksheet Excel interface, you will actually not see it in the list of available macros.

Hit Alt + F8 to get to the Run Macro window:

670af19fb215b313c0b16ece82b0ac25.png

We must type in the name of the macro and then also the value for the argument.

Type in the name of the macro and then a space and then the value that you want to send to the macro.

b7c35d9a86425ccff8a6a7cc5ef74da1.png

Note: the text is surrounded by double quotation marks and then the entire thing is surrounded by single quotation marks.

Click Run and you're done:

1437d364c24ba29bdfa5bb39acb30c2d.jpg

Hit Alt + F8 to run the macro.

The name of the macro will not appear in the window so we must type it in under where it says Macro name .

c8cdffb3c52640860df372b7669ea32c.png

Notice the 5 after the name of the macro. That is how you pass a numeric argument to the macro; simply put a space after the name of the macro and type the number.

Note: the name of the macro and the argument must be surrounded by single quotation marks. Look closely in the above code and you will see this. However, the number does not have to be surrounded by double quotation marks like if you were inputting text for the argument.

Run the macro and you will see the result:

9ee4346686975fc9a6203fd7003fab00.jpg

You follow the same pattern as passing a single argument/value/parameter, except you put a comma between the first and second arguments.

First, I've added a macro that accepts two arguments.

c27e2664cacd7f3ec7256741277b2050.jpg

Hit Alt + F8 to go to the Macro window. You will not see this macro since it accepts arguments.

We must type the name of the macro and then a space and the first argument and then a comma and the second argument.

8af8d6e06650f614ebf76f0c601bca8f.png

Note: the name of the macro and arguments, everything that was typed into the macro window, must be surrounded by single quotation marks; text must additionally be surrounded by double quotation marks. You can see this in the example above.

Run the macro and we see this:

d28e2d40126515844572c6053b553517.jpg

Both values were successfully passed to the macro.

You can have, basically, as many arguments as you need, just follow this pattern for adding additional ones. Also, it doesn't matter if you pass a text or numeric value; just make sure to put all text values within double quotation marks.

You can also pass the value of a cell to a macro.

Hit Alt + F8 to get to the macro window. Remember, you won't see the macro in the list.

Under where it says Macro name , input the name of the macro and the argument just as in previous examples, with one difference this time, we use Evaluate() to get the cell reference into the macro:

bd610fbeea2057d0fc8932bcfd6e7e7f.png

Evaluate() is used to reference a cell; the cell reference inside of Evaluate() must be surrounded by double quotation marks.

Note : everything must be surrounded with single quotation marks.

Run the macro and you should see this:

cbe51c63df63ef3ac8bfc21e4cb14bad.jpg

You can see the value of cell A1 was put into the macro and output in the pop-up message box.

If you have Names in your spreadsheet, you can also pass those to a macro.

First, hit Alt + F8 to get to the Macro window. Since we are passing a value to a macro, the macro won't appear in the window and we will have to type it in following the pattern of the above examples where you have the name of the macro and then the argument to pass to the macro.

b26d9375c6a62b2c7a175e20f518df5e.png

Evaluate() is used to bring the value of the name into the macro. You must put the name inside of Evaluate() and surround the name with double quotation marks. This way, whatever value the name contains or references will be passed to the macro.

Note : everything must be surrounded with single quotation marks, as you can see in the example.

356e0445341d648a978baaf49577b74e.jpg

The output is the same as the last example only because I set the name Cell_Stuff equal to the value of cell A1 on Sheet1.

To assign a macro that you pass arguments to a button, shape, image, or any object, you first right-click that object and click Assign Macro and then type the name of the macro and the argument, following the pattern described in the above examples, and then click OK.

Here is a sample:

be191be239e8c8125e4cef4451488312.png

Note: you must surround everything with single quotation marks and text must always have double quotation marks around it. If you want to send the value of a cell or a name or multiple arguments at once, reference the above examples for this; it works the same calling the macro from a button as it does from the Macro window that you get to by hitting Alt + F8.

Now, whenever I click my button I see this:

8c366add83cd4733ec216265bfff4fdf.jpg

You can also reference the macro like this:

Instead of putting a space after the macro name and then the arguments, you put parentheses and then the arguments inside of them.

The format for the arguments remains the same as for the above examples.

This tutorial covers a lot of examples but, the main thing is to remember to surround everything with single quotation marks, type the name of the desired macro, put double quotation marks around any text arguments, and make sure the macro itself can accept arguments.

Make sure to download the file attached to this tutorial to work with these examples in Excel.

Question? Ask it in our Excel Forum

Excel vba course - from beginner to expert.

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA Course - Beginner to Expert

Subscribe for weekly tutorials, bonus: subscribe now to download our top tutorials ebook.

The link to our top 15 tutorials has been sent to you, check your email to download it!

(If you don't see the email, check your Spam or Promotions folder and make sure to add us as a contact so you get our emails in the future.)

Excel Forum

200+ Video Lessons 50+ Hours of Video 200+ Excel Guides

Save Up to 85% LIMITED TIME OFFER

Project Management Templates

Passing Arguments in Excel VBA

worksheet pass vba

Table Of Contents

Vba code library, vba reference, vba projects, full access with source code.

Ultimate TOC Builder VBA Project

Designed and Developed by PNRao

Full Access with VBA Source Code

Well Commented Codes Lines

Creative and Professional Design

Effortlessly Manage Your Projects

120+ project management templates.

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:

50+ Excel Templates

50+ PowerPoint Templates

25+ Word Templates

We write the procedures to perform certain tasks and some times we may required to write another procedure with small variations. In this situation we can take advantage of the Passing Arguments.

In this Section:

What are By Value (ByVal) and By Reference (ByRef) Passing Arguments?

  • Writing a procedure or a function with Arguments

How to call a function or a procedure?

  • Example macro file to explain passing arguments

We can pass the arguments in two different ways: 1. By Value (ByVal): We will pass the actual value to the arguments 2. By Reference (ByRef): We will pass the reference (address, pointers in other language) to the arguments

ByRef is default passing argument type in VBA. This means, if you are not specifying any type of the argument it will consider it as ByRef type. However, it is always a good practice to specify the ByRef even if it is not mandatory.

Writing a procedure or a function with Arguments:

Syntax for passing arguments:, following example shows how to write a function to add two integers:, following example shows how to write a procedure to multiply two integers:.

  • We can call a function from either a procedure or function or Worksheet.
  • And we can call a procedure from either a procedure or function.

Calling a Function from a Procedure:

Calling a procedure from another procedure:, calling a function from another function:, calling a function from worksheet:, passing arguments in excel vba – example macro file to download.

You can download the example file [ANALYSIS TABS – Passing Arguments.xlsm] and have a look into this.

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

All-in-One Pack 120+ Project Management Templates

Essential pack 50+ pm templates, excel pack 50+ excel pm templates, powerpoint pack 50+ excel pm templates, ms word pack 25+ word pm templates, ultimate project management template, ultimate resource management template, project portfolio management templates, related posts.

VBA ActiveSheet – Excel Active Sheet Object

VBA ActiveSheet – Excel Active Sheet Object

Excel VBA ColorIndex

Excel VBA ColorIndex

Excel VBA Copy Range to Another Sheet with Formatting

Excel VBA Copy Range to Another Sheet with Formatting

I am trying to call the below Sub but getting a syntax error. Sub AddNewWorkbook1(ByVal nPas As String)

‘Adding New Workbook Workbooks.Add nPassword.Copy Range(a1).Select ActiveSheet.Paste ‘Saving the Workbook ActiveWorkbook.SaveAs “C:WorkbookName.xls”

‘calling sub AddNewWorkbook1 (f)

“A1”

Leave A Comment Cancel reply

You must be logged in to post a comment.

Effectively Manage Your Projects and  Resources

ANALYSISTABS.COM provides free and premium project management tools, templates and dashboards for effectively managing the projects and analyzing the data.

We’re a crew of professionals expertise in Excel VBA, Business Analysis, Project Management. We’re Sharing our map to Project success with innovative tools, templates, tutorials and tips.

Project Management

Download Free Excel 2007, 2010, 2013 Add-in for Creating Innovative Dashboards, Tools for Data Mining, Analysis, Visualization. Learn VBA for MS Excel, Word, PowerPoint, Access, Outlook to develop applications for retail, insurance, banking, finance, telecom, healthcare domains.

Analysistabs Logo

© 2023 Analysistabs | Sitemap | Your Privacy | Terms

Power Spreadsheets

Excel and VBA tutorials and training. Learn how to use Microsoft Excel and Visual Basic for Applications now.

Excel VBA Protect Sheet with Password in 2 Easy Steps (+ Free Easy-To-Adjust Excel Workbook Example)

Excel VBA Protect Sheet with Password in 2 Easy Steps (+ Free Easy-To-Adjust Excel Workbook Example)

This Excel VBA Protect Sheet with Password Tutorial is accompanied by an Excel workbook with the data and VBA code I use when describing the step-by-step process below. Get this example workbook (for free) by clicking the button below .

Get immediate free access to the Excel VBA Protect Sheet with Password workbook example

The VBA code in the Excel workbook that accompanies this Excel VBA Protect Sheet with Password Tutorial is (always) stored in the Visual Basic Editor (VBE). If you don't know how to work with the VBE, I suggest you read my Visual Basic Editor (VBE) Tutorial. I link to this Tutorial in the Related Excel Macro and VBA Training Materials and Resources Section below.

Table of Contents

Related Excel Macro and VBA Training Materials and Resources

The following Excel Macro and VBA Tutorials may help you better understand and implement the contents below.

  • Excel Macros: Click here to open .
  • Excel VBA: Click here to open .
  • Enable macros in Excel: Click here to open .
  • Work with the Visual Basic Editor (VBE): Click here to open .
  • Create Sub procedures: Click here to open .
  • Refer to objects ( click here to open ), including sheets ( click here to open ).
  • Methods: Click here to open .
  • Data types: Click here to open .
  • Loops: Click here to open .
  • Activate workbook: Click here to open .
  • Create new workbook: Click here to open .
  • Open workbook: Click here to open .
  • Delete sheet: Click here to open .

This Excel VBA Protect Sheet with Password Tutorial is part of a more comprehensive series of Excel VBA Protect or Unprotect Sheet Tutorials.

  • Excel VBA Protect Sheet Without Password in 2 Easy Steps: Click here to open .
  • Excel VBA Unprotect Sheet Without Password in 2 Easy Steps: Click here to open .
  • Excel VBA Unprotect Sheet with Password in 2 Easy Steps: Click here to open .
  • Excel VBA Protect Sheet Allow Filter in 2 Easy Steps: Click here to open .
  • Excel VBA Protect Sheet Allow Select Locked Cells in 4 Easy Steps: Click here to open .

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to visit the Archives .

If you want to learn how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

  • Premium Courses at the Power Spreadsheets Academy: Click here to open .
  • Books at the Power Spreadsheets Library: Click here to open .
  • VBA Cheat Sheets: Click here to open .

If you want to save time when working with macros and VBA, you may be interested in AutoMacro: Click here to learn more about AutoMacro  (affiliate link). AutoMacro is an add-in for VBA that installs directly into the VBE. Depending on the version, AutoMacro comes loaded with:

  • Code generators.
  • An extensive code library.
  • The ability to create your own code library.
  • Advanced coding tools.

If you need consulting services, you may want to consider working with ExcelRescue. ExcelRescue is my usual suggestion for people who (like you) may need help with Excel tasks/projects: Click here to visit ExcelRescue (affiliate link).

The VBA Protect Sheet with Password Snippet Template/Structure

The following is the VBA protect sheet with password snippet template/structure I explain (step-by-step) in the Sections below.

The Example Before VBA Protect Sheet with Password

The VBA code in the Excel workbook that accompanies this Excel VBA Protect Sheet with Password Tutorial is (always) stored in the Visual Basic Editor (VBE). If you don't know how to work with the VBE, I suggest you read my Visual Basic Editor (VBE) Tutorial. I link to this Tutorial in the Related Excel Macro and VBA Training Materials and Resources Section above.

The example workbook has a single (empty) worksheet.

  • The name of the example worksheet is “Protect Sheet with Password”.
  • This is the sheet the VBA protect sheet with password example macro I create (by following the step-by-step process below) works with. In other words: The VBA protect sheet with password example macro protects this sheet with a password.

The image below displays the example worksheet before I execute the VBA protect sheet with password example macro.

Notice the Protect Sheet button (inside the Protect group of commands) in the Excel Ribbon (indicating the example worksheet isn't (currently) protected).

Example Excel workbook before VBA protect sheet with password example macro

Step 1: Refer to Sheet

Refer to the sheet you want to protect with password.

In other words: Create a VBA expression that returns an object representing the applicable sheet (you want to protect with password). As a general rule: Work with 1 of the following objects:

  • A Worksheet object, representing a worksheet.
  • A Chart object, representing a chart sheet.

Consider explicitly including the following references to create a fully qualified object reference returning the applicable Worksheet (representing a worksheet) or Chart (representing a chart sheet) object:

  • The Application.ThisWorkbook property.
  • The Application.Workbooks and Workbooks.Item properties.
  • The Application.ActiveWorkbook property.
  • The Workbook.Sheets and Sheets.Item properties.
  • The Workbook.Worksheets and Worksheets.Item properties.
  • The Workbook.Charts and Charts.Item properties.
  • The Application.ActiveSheet property.
  • The Workbook.ActiveChart property.

Step 1 Example

  • Refer to the worksheet named “Protect Sheet with Password” inside the workbook where the procedure is stored.
  • The Application.ThisWorkbook property: ThisWorkbook.
  • The Workbook.Worksheets and Worksheets.Item properties: Worksheets(“Protect Sheet with Password”).

Step 2: Protect Sheet and Specify Password

Do the following to:

  • Protect the sheet (you refer to in step #1); and
  • Specify the protection password.

(1) Start with the Worksheet or Chart object reference you created in step #1.

(2) Call the applicable version of the Protect method:

  • Worksheet.Protect, if protecting a worksheet.
  • Chart.Protect, if protecting a chart sheet.

(3) Use the Password parameter of the Protect method to specify the protection password.

  • Specify the password as a string.
  • The password you specify is case sensitive.

Step 2 Example

I do the following:

(1) Start with the Worksheet object reference I created in step #1.

(2) Call the Worksheet.Protect method.

(3) Use the Password parameter of the Worksheet.Protect method to specify the protection password as “Protect Sheet with Password”.

The full VBA protect sheet with password example macro is as follows:

The GIF below illustrates the effects of using the VBA protect sheet with password example macro.

Notice how:

  • The Protect Sheet button (inside the Protect group of commands) in the Excel Ribbon (indicating the example worksheet isn't (currently) protected);
  • Is replaced by the Unprotect Sheet button (indicating the example worksheet is now protected) when I execute the VBA protect sheet with password example macro.

Download the VBA Protect Sheet with Password Example Workbook

This Excel VBA Protect Sheet with Password Tutorial is accompanied by an Excel workbook with the data and VBA code I use when describing the step-by-step process above. Get this example workbook (for free) by clicking the button below .

The following Excel Macro and VBA Tutorials may help you better understand and implement the contents above.

guest

I publish a lot of Tutorials and Training Resources about Microsoft Excel and VBA . Here are some of my most popular Excel Training Resources:

  • Free Excel VBA Email Course
  • Excel Macro Tutorial for Beginners
  • Excel Power Query (Get and Transform) Tutorial for Beginners
  • Excel Keyboard Shortcut Cheat Sheet
  • Excel Resources

worksheet pass vba

Excel: What are macros and what do they do

E xcel macros increase productivity and save time when creating a complex workbook or worksheet. Even if you start with some of the best Microsoft Excel templates , adding macros simplifies using your spreadsheet in the future. Here's an explanation of what Excel macros are, what you can do with a macro, how to write and record macros to customize your workbook, and how to run macros to automate and speed up repetitive tasks.

Excel macros defined

An Excel macro is a series of commands stored in a workbook. A macro can replace actions that are as simple as a few menu choices and mouse clicks. By recording your actions, you can assign macros and replay them again with a tap of a shortcut key.

More complicated macros might be a complete program written in the Visual Basic editor using VBA code. VBA stands for Visual Basic for Applications, a programming language that includes special commands specific to Excel.

When you open macro-enabled workbooks, which are Excel files with an XLSM extension, Microsoft Office alerts you with a security warning. Macros can be dangerous and contain malicious code, so use caution.

If you don't know the origin of a file or are unsure if the creator is trustworthy, don't activate the macros. If you know the file is safe and want to use the VBA macros, choose Enable Content in the security warning.

You can bypass this warning by turning on macros in Microsoft Office Trust Center settings. Make sure you understand the risks if you do so. In the File tab , select Options , then choose Trust Center .

What can I do with Excel macros?

Excel macros are helpful. You can use macros to apply formatting to cells, rows, columns, and worksheets, sort and filter information, and perform data analytics to get a workbook ready for use.

Macros can build charts, graphs, and tables for creating reports. It imports or exports the data from other sources. Excel VBA macros can also generate documents and send emails, cutting your work to a minimum.

How to create, record, and run Excel macros

How to record a macro.

In short, open the Developer tab, select the Record Macro button, and type a macro name. You can store a macro in a Personal Macro Workbook that's available from any worksheet or in This Workbook if you only want to use it on your current worksheets.

Excel records your actions. Perform the steps you want to repeat when you run the macro in the future. Click the Stop Recording button when you're done. That way, you won't add unnecessary steps.

If you don't see the Developer tab, right-click the Excel ribbon and choose Customize the Ribbon from the drop-down menu. Tick the check box for Developer . The Developer tab appears between the View and Help tabs.

How to write VBA code

If you want to learn Visual Basic for Applications, start with our guide to creating Excel macros . If you're a beginner, unfamiliar with VBA, or want to learn more about VBA for Excel, Microsoft's macro tutorial is a good resource.

If you know VBA, open the Developer tab and select Visual Basic from the toolbar to open the VBA Editor. Alternatively, press Alt + F11 to toggle back and forth between the VBA Editor and your Excel workbook.

You can assign a keyboard shortcut to Excel macros written in VBA code, just as you can with recorded macros. Since you're using macros to save time, triggering them with keystrokes is a great idea.

What platforms support Excel macros?

You can only create Excel macros on the desktop version of Excel. The desktop version of Excel is available for Windows PCs and Mac computers. While you can't make new macros in Excel online, you can run macro-enabled workbooks. The Excel mobile app cannot run macros.

You can also use Excel Online to run macros on an inexpensive and long-lasting Chromebook . However, Microsoft didn't make a desktop version for ChromeOS. If you want to record macros or create code for a spreadsheet, you'll need another solution.

More macro options

Google Sheets has macro recording and an Apps Script Editor where you can write custom code. It's hard to beat Microsoft Excel, but Google Sheets is a worthy competitor that might be able to handle most of your needs.

How to use Microsoft 365 (Office) on a Chromebook

Excel: What are macros and what do they do

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Worksheet.Protect method (Excel)

  • 8 contributors

Protects a worksheet so that it cannot be modified.

expression . Protect ( Password , DrawingObjects , Contents , Scenarios , UserInterfaceOnly , AllowFormattingCells , AllowFormattingColumns , AllowFormattingRows , AllowInsertingColumns , AllowInsertingRows , AllowInsertingHyperlinks , AllowDeletingColumns , AllowDeletingRows , AllowSorting , AllowFiltering , AllowUsingPivotTables )

expression A variable that represents a Worksheet object.

In previous versions , if you apply this method with the UserInterfaceOnly argument set to True and then save the workbook, the entire worksheet (not just the interface) will be fully protected when you reopen the workbook. To re-enable the user interface protection after the workbook is opened, you must again apply this method with UserInterfaceOnly set to True .

If you want to make changes to a protected worksheet, it is possible to use the Protect method on a protected worksheet if the password is supplied. Also, another method would be to unprotect the worksheet, make the necessary changes, and then protect the worksheet again.

Unprotected means that the cell may be locked ( Format Cells dialog box) but is included in a range defined in the Allow Users to Edit Ranges dialog box, and the user has unprotected the range with a password or has been validated via NT permissions.

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Was this page helpful?

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback .

Submit and view feedback for

Additional resources

worksheet pass vba

IMAGES

  1. VBA WorkSheets

    worksheet pass vba

  2. Excel Vba Worksheet Function

    worksheet pass vba

  3. VBA Name Worksheet

    worksheet pass vba

  4. Activate A Worksheet Vba

    worksheet pass vba

  5. Excel Vba Password Protect Worksheet Viewing Worksheet : Resume Examples

    worksheet pass vba

  6. Article: Vba Pass Worksheet Name To Sub

    worksheet pass vba

VIDEO

  1. Hop Travel vs Jump Stop

  2. Digital India Portal, CSC के लिए apply कैसे करें।, वसुधा केन्द्र लेने के लिए कैसे करें।

  3. Excel tip to visualize pass fail with thumbs up and thumbs down

  4. HƯỚNG DẪN PHÁ PASS VBA

  5. HNDA

  6. Workbooks

COMMENTS

  1. pass sheet to a function (excel vba)

    6. I've been trying to pass a sheet in my workbook to a function, but it doesn't seem to be working. The function is. Private Sub passToSheet(theData As Variant, Optional mySheet As Worksheet) I have tried doing. Dim mySheet as Worksheet. Set mySheet = "Results". then to call the function. passToSheet theData mySheet.

  2. vba

    If you're trying to refer to a sheet in the workbook you're about to open, you'll have to pass the sheet name as a String argument instead. - Rory. Nov 24, 2015 at 16:24. 1. Further to what @Rory said, if you pass the name as a string, you'll still have to set the worksheet in the sub ... like Set ws = TempBook.Sheets(sName) assuming sName is ...

  3. Working with Worksheets using Excel VBA (Explained with Examples)

    For example, suppose you have a workbook with three worksheets - Sheet 1, Sheet 2, Sheet 3. And you want to activate Sheet 2. You can do that using the following code: Sub ActivateSheet() Worksheets("Sheet2").Activate. End Sub. The above code asks VBA to refer to Sheet2 in the Worksheets collection and activate it.

  4. Passing a Worksheet into a Sub()

    Re: Passing a Worksheet into a Sub (), HELP! Just pass the argument along: Code: Myfile = Application.GetOpenFilename(Title:=Title) You can probably see why it's better to use a variable name that isn't also the name of a parameter. 0.

  5. VBA Sheets

    Worksheets. This is the ultimate guide to working with Excel Sheets / Worksheets in VBA. At the bottom of this guide, we've created a cheat sheet of common commands for working with sheets. Sheets Vs. Worksheets. There are two ways to reference Sheets using VBA. The first is with the Sheets object: 99% of the time, these two objects are ...

  6. Use Worksheet Functions in a Macro

    How to Use Worksheet Functions in VBA. To access an Excel function in VBA add Application.WorksheetFunction in front of the function that you wish to call. In the example below, we'll call Excel's Max Function: Dim maxvalue as long. maxvalue = Application.WorksheetFunction.Max(Range("a1").Value, Range("a2").Value)

  7. The Complete Guide To The VBA Worksheet

    1 A Quick Guide to the VBA Worksheet. 2 Introduction. 3 Accessing the Worksheet. 3.1 Hide Worksheet. 3.2 Protect Worksheet. 3.3 Subscript Out of Range. 4 Using the Index to Access the Worksheet. 5 Using the Code Name of a Worksheet. 5.1 Code Name in other Workbooks.

  8. Worksheet object (Excel)

    The worksheet index number denotes the position of the worksheet on the workbook's tab bar. Worksheets(1) is the first (leftmost) worksheet in the workbook, and Worksheets(Worksheets.Count) is the last one. All worksheets are included in the index count, even if they are hidden. The worksheet name is shown on the tab for the worksheet.

  9. Using Excel worksheet functions in Visual Basic

    Office VBA reference topic. Calling a worksheet function from Visual Basic. In Visual Basic, the Excel worksheet functions are available through the WorksheetFunction object.. The following Sub procedure uses the Min worksheet function to determine the smallest value in a range of cells. First, the variable myRange is declared as a Range object, and then it is set to range A1:C10 on Sheet1.

  10. VBA ActiveSheet & How To Activate or Select a Sheet

    To set the ActiveSheet use Worksheet.Activate: Worksheets("Input").Activate. The Activate Sheet command will actually "go to" the sheet, changing the visible Sheet. The above example uses the Sheet (Tab) name. Instead you can use the VBA code name for the worksheet:

  11. Passing arguments efficiently (VBA)

    Passing arguments efficiently. All arguments are passed to procedures by reference, unless you specify otherwise. This is efficient because all arguments passed by reference take the same amount of time to pass and the same amount of space (4 bytes) within a procedure regardless of the argument's data type. You can pass an argument by value if ...

  12. Pass Arguments to a Macro Called from a Button or Sheet in Excel

    Call the Macro from a Button or Other Object/Shape. To assign a macro that you pass arguments to a button, shape, image, or any object, you first right-click that object and click Assign Macro and then type the name of the macro and the argument, following the pattern described in the above examples, and then click OK.

  13. Passing Arguments in Excel VBA

    We can pass the arguments in two different ways: 1. By Value (ByVal): We will pass the actual value to the arguments. 2. By Reference (ByRef): We will pass the reference (address, pointers in other language) to the arguments. ByRef is default passing argument type in VBA. This means, if you are not specifying any type of the argument it will ...

  14. VBA Worksheets

    Step 1: Start the sub-procedure by naming the macro. Step 2: Inside the sub-procedure, enter the Worksheets object name, and we can see that in the IntelliSense list. Step 3: Open the parenthesis for the Worksheet object, and you can see the syntax for the VBA Worksheet. Step 4: Assume we must activate the worksheet "Sheet2.".

  15. Excel VBA Protect Sheet with Password in 2 Easy Steps (+ Example)

    Step 1: Refer to Sheet. Refer to the sheet you want to protect with password. In other words: Create a VBA expression that returns an object representing the applicable sheet (you want to protect with password). As a general rule: Work with 1 of the following objects: A Worksheet object, representing a worksheet.

  16. Excel: What are macros and what do they do

    Excel macros are helpful. You can use macros to apply formatting to cells, rows, columns, and worksheets, sort and filter information, and perform data analytics to get a workbook ready for use ...

  17. Hide/Unhide Worksheets Based on Name & Password

    Additionally, rather than having to change usernames and passwords in VBA, I would like to have a hidden worksheet that an administrator can go in to add/delete/modify usernames, passwords and the worksheets each user has access to. Not sure if this is possible but if so it is definitely beyond me and I would appreciate any assistance. Thanks, C

  18. Worksheet.Protect method (Excel)

    Variant. A string that specifies a case-sensitive password for the worksheet or workbook. If this argument is omitted, you can unprotect the worksheet or workbook without using a password. Otherwise, you must specify the password to unprotect the worksheet or workbook. If you forget the password, you cannot unprotect the worksheet or workbook.

  19. VBA Function

    You can also create functions that refer to objects in VBA but you need to use the Set Keyword to return the value from the function. Function GetRange() as Range. Set GetRange = Range("A1:G4") End Function. If you were to use the above function in your VBA code, the function would always return the range of cells A1 to G4 in whichever sheet ...

  20. VBA Protect / Unprotect Worksheets

    Protect Worksheets. Worksheet Protection allows you to lock certain aspects of the sheet from editing. This menu is found in Home > Format > Protect sheet or by right-clicking on the Sheet tab name: Most commonly this is used to protect "Locked" cells from editing, only allowing the end-user to edit certain cells.

  21. VBA Compile Error for each control variable must be variant or object

    Point 4: I strongly recommend that each of the modules into which you place VBA code (standard modules, workbook modules, worksheet modules, UserForm modules, etc.) should start with an Option Explicit statement. That statement forces you to declare any variables you use, which avoids most bugs due to misspelling of variable names.

  22. Formula to get address of selected cell on a worksheet

    The name box shows Z100 so the formula in A1 would return "Z100". There's a few obstacles: - theoretically, a formula capable of doing this would need to recalculate on selection change. A formula that's somewhat related to this is: =INFO ("ORIGIN") The above cell returns the top-leftmost visible cell on the sheet.

  23. excel

    Delete the whole node and save the file. Select all of the files at the root of the folder that you extracted the files to, right click it and click Send to -> Compressed (zipped) folder. Renamed the resulting file to it's original .xlsx/.xlsm extension. Open the file, and the protection on that sheet will be gone.

  24. Excel spreadsheet password cracking using VBA

    Followed the following steps: Developer --> Record Macro (give a name, then do some clicks) Macros --> take the macro you created for edit. Replace the Macro with the whole function below: Sub PasswordBreaker() 'Breaks worksheet password protection. Dim i As Integer, j As Integer, k As Integer.