Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in


How to select all sheets except one in Excel?

We can apply the Select All Sheets feature to select all sheet tabs of a workbook, but, do you have any ideas to select all sheets except the specific one in Excel?

Select all sheet tabs except specific one with VBA code

Enable you to do smarter, faster and better

Kutools - the best office productivity tool brings 300 advanced features to Excel. Details... Download...

Office Tab - brings tabbed interface to Excel and Word, make your work much easier. Details...Download...

arrow blue right bubble Select all sheet tabs except specific one with VBA code

To select all tabs except specific one, the following VBA code can help you, please do as this:

1. Hold down the ALT + F11 keys, then it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Select all sheets except one:

Sub selectallbutone()
'Updateby Extendoffice 20161215
Dim x As Long
For x = 2 To ThisWorkbook.Sheets.Count
    If Sheets(x).Name <> "Sheet5" Then Sheets(x).Select Replace:=False
Next x
End Sub

Note: In the above code, Sheet5 is the sheet name that you want to exclude when selecting all sheets, please change it to your need.

3. And then press F5 key to run this code, and all sheet tabs have been selected except the specific one, see screenshot:

doc select all tabs except one 1

Excel Productivity Tools

Kutools for Excel - The Best Office Productivity Tool

  • Brings 300 powerful advanced features to Excel, enable you to do smarter, faster and better.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Office Tab - brings tabbed interface to Office, make your work much easier.

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.