Happy Monday!!. Time for some more Monday Macro fun. I wanted to share this little but powerful Macro with you today, I used this myself just last week so thought you would like to have this in your Excel Macro toolkit. Are you an Excel user looking for a quick and easy way to unprotect multiple worksheets in your workbook at once? Having to manually unprotect one worksheet after another can be time consuming and tedious. With the help of this tutorial, you’ll learn how to create a macro that you can use with just two clicks of your mouse to instantly unlock all the sheets in any Excel file. Get ready to streamline your workflow and save precious time!

I had a workbook with 14 worksheets all protected, as they all contained a lot of formulas which I did not want any users of the workbook to be able to edit in any way.  A small piece of code unlocked them all at the same time.

What Does The Macro Do?

The Macro will use a message box to prompt the user to enter the password to unlock all of the worksheets protected in the Excel workbook.  Excel will loop through all of the worksheets and unlock them if the password is correct.  If the password is incorrect then a message box will appear to user informing them, with a message we have created.

How Does It Work?

FIRST. Open Visual Basic – by hitting ALT +F11 or Developer Tab – Visual Basic – Insert Module

Step 1. We need to declare some variables. We need to declare the password as variant  as it could contain any type of, or a mix of characters and worksheet is declared also as ws.  This will create a memory container in Excel for each worksheet the macro loops through.

Step 2. Next, if the user enters an incorrect password, an error message box is prompted to inform the user of the message we choose in Step 5.

Step 3. The macro begins with a input box prompting the user for their password and Excel begins at the first work sheet in the workbook,  using the password supplied by the user in the input box and un-protects the worksheet.

Step 4. Excel then loops through the next worksheet and so on until all worksheets have been completed and the macro ends.

Step5. We can choose what ever we want to the error to say, if the error routine is prompted by the user by entering an incorrect password.

Step 6. Test Your Macro!

Want The Code? You can copy it right here to unprotect multiple Excel worksheets

Sub UnprotecyMySheets()
Dim password As Variant
Dim ws As Worksheet
On Error GoTo error
password = InputBox(“Password Please”)
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect password:=password
Next
Exit Sub
error: MsgBox “There is s problem  with you Password – Please try again”
End Sub

Dont forget to SUBSCRIBE to the How To Excel At Excel YouTube Channel for more Excel solutions

 

• A Super Quick Way To Unprotect Multiple Excel Worksheets
• Protect All Excel Worksheets At The Same Time.
• A VBA Excel Macro Which Will Export And Save Each Worksheet As A Separate PDF – Macro Mondays
• Macro Mondays – How To Protect Your Excel VBA Code
• Macro Mondays – Automatically Protect A Worksheet When You Close Your Excel Workbook

Hello Excellers, time for another #macromonday blogpost. In this tutorial we will look at VBA and workbooks in Excel. If you’re like me, you have a ton of Excel workbooks open simultaneously. And it can be tough to keep track of how many are open, especially if you’re working on a project with multiple workbooks. Luckily, there’s an easy way to count how many Excel workbooks are open using a macro. In this blog post, I’ll show you how to create a VBA macro that counts the number of open workbooks, and I’ll also share a few tips on how to use the macro. So read on to learn more!. This simple macro will display a message box to the user with a count of how many Excel workbooks are open.

So, first, let’s write some code, and tuck it away in our Personal Macro Workbook and we can re use that code whenever we need it. Let’s count how many open workbooks there are.

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share three free Excel Tips on the every monthmonth and receive my free Ebook, 50 Excel Tips.

If you want to see all of the blog posts in the Macro Mondays Series or the example worksheets you can do so by clicking on the links below.

 How To Excel At Excel – Macro Mondays Blog Posts.

So, What Does The Macro Do?

This Macro uses the Workbooks.Count property which returns the number of open Excel Workbooks.  For ease, we then get Excel to display the result of this in a handy message box using the MsgBox Function. This macro is so simple it hardly needs a walkthrough!

 

Step 1. Starting The VBA Macro To Count Open Workbooks.

First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.

When you decide to write an Excel macro, you need to choose where to store it. You have two choices.

1. Store it in the current Excel workbook you are working on creating.
2. Save the macro in your Personal Macro workbook.

As I already know that this macro is a handy one I will store in in my Personal Macro workbook,

Step 2. Name Your Macro

The first step in writing the VBA macro to count open workbooks is to name macro. Stert by typing Sub then the name of the macro. In this example I keep it simple with CountMyWorksbooks(). Notice once you hit enter on the name of the macro Excel automatically inserts the End Sub line of code. Any other lines of VBA code should be written between these two lines.

Step 3. Use The Message Box Function

The only line of code required is the message box function in this example to DISPLAY information to the user. The syntax of the MsgBox is simple

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

The only required part of the syntax is the prompt. All others are optional. Indicated by the [ ] brackets. In this example it is the result of the Workbooks.Count calculation.

Step 4. Ending The Macro

Once the MsgBox has displayed the value, then Macro ends.

This is a really short macro, but surprisingly handy. We could go a little further and customise the message box. We have only just used the minimum and required part of the function. So we can go ahead and type a title to be displayed to give a little more context for the user.

By amending the code slightly and adding in the Title part of the code,we can add and display some context. When we run the macro again, it makes a little more sense. ( You could type any text for your users to see in the Title Part of the MsgBox function).

• Macro Mondays -Open An Excel Workbook To A Specific Worksheet Tab
• Macro Mondays – A Quick Macro To Open A Spreadsheet To A Particular Sheet And Cell
• How To Excel At Excel – Macro Mondays Blog Posts.
• Delete Data, Keep Formulas with an Excel Macro
• Open A New Version Or Copy Of Your Excel Workbook.

If you have many pivot table with filters, applying changes to them all can be time-consuming. Fortunately, there is an easier way! Using a macro in Microsoft Excel, you can quickly change all the filter settings at once. At the end of this article you be able update your pivot table filters at once with VBA. How awesome is that?. Let’s get started.

Example Use Case of Changing All Pivot Table Filters at Once.

For two reports I create monthly, I have approximately fifteen worksheets all with pivot Tables.  None of these pivots are controlled by a slicer (at the design request of the user) but all pivot tables need to be updated every month to show the new monthly figures, I really do not want to have to change all of the pivot table filters month to month individually, so this Excel VBA Macro is super useful.

What Does The Macro Do?

This macro changes all pivot table Filters in all pivot tables in your active workbook. Specify in the VBA code the value of the filter.

How Does The Macro Work?

Step 1. Open Visual Basic, Insert A Module and Name Your Macro.

Firstly, open the Visual Basic Editor. There are two ways to do this

• Hit ALT+F11 or
• Developer Tab | Visual Basic

Insert a new module into the current Excel workbook by selecting This Workbook, Insert Menu then Module.

So, the final part of step 1 is to name your Excel macro. Type Sub and then name of the macro. In this example the name of the macro is ChangePivotFilter. Accordingly, Excel automatically inserts the End Sub line of code. All other lines of code are stored between these two lines.

Step 2. Declare Variables.

Next, the second step is to declare any variables in the macro. Declaring a variable creates a memory container for that value. In this case example variables are declared for worksheets and pivot tables which Excle will loop through in the code. The declared variables can be seen below.

Dim WS As Worksheet, Dim WB As Workbook, Dim myPivot As PivotTable, Dim myPivotField As PivotField

Step 3. Set Code To Run On Current Workbook.

This line of code ensures only the current active workbook is updated with this code. No other Excel workbooks will be affected by running the VBA code.

Set aWB = ActiveWorkbook

Step 4. Loop Through All Pivot Table Filters

Next any current pivot table flters are cleared. In addition, any errors that occur Excel moved to the next line of code. The for next loop is used to loop through all pivot table filters, resetting them in preparation for the next line of code.

For Each WS In aWB.Worksheets
For Each myPivot In WS.PivotTables
Set myPivotField = Nothing
On Error Resume Next

Step 5. Set The Pivot Table Filters

The next line of code is where the pivot table filter is updated with VBA. In this example the filters change ‘Sales Person’ to ‘Julie’. Change this line of code the the filter required. Each worksheet and pivot table is looped until all pivot table filters are updated. Finally, the procedure then ends. The final part of this process is to test the macro.

Set myPivotField = myPivot.PivotFields(“Sales Person”)
myPivotField.CurrentPage = “Julie”
Next myPivot
Next WS
End Sub

Finally, copy the code below, all you need to do is replace the name of the Pivot Table Field and the Pivot Table Filter.

Sub ChangePivotFilter()
Dim WS As Excel.Worksheet, aWB As Excel.Workbook, myPivot As Excel.PivotTable,myPivotField As Excel.PivotField
Set aWB = ActiveWorkbook
For Each WS In aWB.Worksheets
For Each myPivot In WS.PivotTables
Set myPivotField = Nothing
On Error Resume Next
Set myPivotField = myPivot.PivotFields(“Sales Person”)
myPivotField.CurrentPage = “Julie”
Next myPivot
Next WS
End Sub

So, changing all the filters in your pivot tables at once can save you a lot of time, and this macro makes it super easy. Be sure to sign up for my newsletter so you don’t miss any more tips like this, and subscribe to my YouTube channel for more helpful videos.

Lastly, More Excel Articles.

• Break Your Pivot Table Filters Into Multiple Columns
• Easy Macro. Trim Excel Worksheet Names.
• Macro Mondays – Delete All Rows That Contain A Specific Text String
• How To Show or Filter Duplicates In An Excel Pivot Table.
• Re-Start That Pivot Table Without Deleting