在Excel中显示特定的工作表

In a workbook with lots of worksheets, it can be hard to find the ones that you need, to get a specific task done. Instead of scrolling through all the sheet tabs, or using the popup list of worksheets, use a drop down list to show just a few specific sheets in Excel. You’ll be able to focus on what you need to do, and ignore everything else.

在包含大量工作表的工作簿中，很难找到您需要的工作表来完成特定的任务。 而不是滚动浏览所有工作表选项卡，也不使用工作表的弹出列表，而使用下拉列表仅显示Excel中的一些特定工作表。 您将能够专注于需要做的事情，而忽略其他所有事情。

选择图纸类型 (Select a Sheet Type)

On the Menu sheet, there is a data validation drop down list. Select a sheet type from the drop down list, and only the Menu sheet, and sheets with the selected text in their name are visible.

在菜单表上，有一个数据验证下拉列表 。 从下拉列表中选择一种图纸类型，只有“菜单”图纸和名称中带有所选文本的图纸可见。

For example, choose "computer" and all sheets with "computer" in their name are visible, along with the Menu sheet. All other sheets are hidden.

例如，选择“计算机”，其名称中带有“计算机”的所有工作表以及菜单表均可见。 所有其他工作表都被隐藏。

If you choose ALL as the Sheet Type, all the sheets in the workbook are made visible.

如果选择“全部”作为“图纸类型”，则工作簿中的所有图纸都将变为可见。

下拉列表 (The Drop Down List)

On another worksheet, there is a list of sheet types, formatted as a named Excel table. The data in that table was selected, and named as SheetTypes.

在另一个工作表上，有一系列工作表类型，其格式设置为命名的Excel表。 选择该表中的数据，并将其命名为SheetTypes 。

On the Menu sheet, the drop down list is based on that named range, and the cell with the drop down is named SelectType

在菜单表上，下拉列表基于命名的范围，并且带有下拉列表的单元格名为SelectType。

You could add more items to the SheetTypes list, and the list will expand automatically to include them. Sort the list A-Z, after adding new items – there is a space character at the start of “ ALL”, so it will always sort to the top of the list.

您可以将更多项目添加到SheetTypes列表中，列表将自动扩展以包括它们。 添加新项后，对列表AZ进行排序–“ ALL”开头有一个空格字符，因此它将始终排在列表顶部。

菜单工作表代码 (The Menu Worksheet Code)

On the Menu sheet, there is Worksheet_Change code, that runs when any change is made to the sheet. If the SelectType cell was changed, a macro runs. If “ ALL” was selected, a macro runs to show all the sheets. If a different type was selected, it runs a macro to show only the sheets with that text in their name. The Menu sheet is left visible too.

在菜单表上，有工作表_更改代码，该代码在对表进行任何更改时运行。 如果更改了SelectType单元格，则会运行一个宏。 如果选择“ ALL”，则运行宏以显示所有图纸。 如果选择了其他类型，它将运行一个宏以仅显示名称中带有该文本的图纸。 菜单表也保持可见。

下载样本文件 (Download the Sample File)

To test the macros, you can download the sample file with the code to show specific sheets in Excel. Go to the Excel Files page on my website,  and in the UserForms section, look for UF0034 - Show Specific Sheets

要测试宏，可以下载带有代码的示例文件以在Excel中显示特定的工作表。 转到我网站上的“ Excel文件”页面，然后在“用户窗体”部分中查找“ UF0034-显示特定工作表”

视频：在Excel中显示特定的工作表 (Video: Show Specific Sheets in Excel)

Watch this video to see how the sheet selector works, and for a brief explanation of the VBA code to show specific sheets in Excel.

观看此视频以了解工作表选择器的工作原理，并简要说明了VBA代码以在Excel中显示特定工作表。

演示地址

0:00 Introduction

0:00简介

0:19 Select a Sheet Type

0:19选择图纸类型

0:56 Set Up the List

0:56设置列表

1:55 Find a Command

1:55查找命令

2:17 View the VBA Code

2:17查看VBA代码

3:29 View the Macro Code

3:29查看宏代码

4:59 Get the Sample File

4:59获取示例文件

翻译自: https://contexturesblog.com/archives/2016/06/16/show-specific-sheets-in-excel/