There is a sample Excel file on the Contextures website that has a list of orders, and sales rep names. It has a macro to filter Excel data onto multiple sheets. You can click a button, and a sheet is created for each sales rep, with that person's orders.

Contextures网站上有一个示例Excel文件，其中包含订单和销售代表名称的列表。 它具有一个宏，可将Excel数据过滤到多张纸上。 您可以单击一个按钮，然后为每个销售代表创建一张带有该人订单的工作表。

There is another version of the file, and it creates a sheet for each sales rep name that is visible, after an AutoFilter has been applied.

该文件还有另一个版本，在应用了AutoFilter之后，该文件会为每个可见的销售代表名称创建一个工作表。

这个怎么运作 (How It Works)

When you click the worksheet button, a macro runs. It sets up a list of sales rep names, then loops through that list, creating a worksheet for each name.

当您单击工作表按钮时，将运行一个宏。 它设置了一个销售代表名称列表，然后循环浏览该列表，为每个名称创建一个工作表。

To get the orders onto each sheet, the macro uses an Advanced Filter, setting up a criteria range in cells L1:L2 on the worksheet.

要将订单获取到每张纸上，宏使用Advanced Filter ，在工作表上的单元格L1：L2中设置标准范围。

In the screen shot below, the first name from the list, Gill, has been entered into the criteria range.

在下面的屏幕快照中，列表中的名字Gill已输入到标准范围内。

进阶筛选条件问题 (Advanced Filter Criteria Problems)

The filter works well, unless there is another name that begins with the same characters as one of the full names. In the screen shot below, the orders for Giller have been included on the Gill orders sheet.

除非有另一个名称以全名之一开头的相同字符，否则此过滤器将很好地工作。 在下面的屏幕快照中，吉勒的订单已包含在吉勒的订单表中。

A Giller sheet was also created, and it has the correct list of orders for Giller.

还创建了一个Giller工作表，它具有Giller的正确订单清单。

Why did this happen? The Advanced Filter assumes that we are looking for anything that BEGINS with "Gill", so it includes Giller. It's as if there is an invisible "*" wildcard character at the end of the Sales Rep name.

为什么会这样？ 高级筛选器假定我们正在寻找任何以“ Gill”开头的内容，因此它包含Giller。 好像在销售代表名称的末尾有一个不可见的“ *”通配符。

To get an exact match only, we can change the criteria cell, so it uses an equal sign before the name. To do this manually, we would enter this formula in cell L2:

为了仅获得完全匹配，我们可以更改条件单元格，因此它在名称前使用等号。 要手动执行此操作，我们将在单元格L2中输入以下公式：

="=" & "Gill"

=“ =”＆“吉尔”

在VBA代码中解决问题 (Fix the Problem in the VBA Code)

In the original Excel VBA code, the sales rep's name is entered into cell L2.

在原始Excel VBA代码中，销售代表的名称输入到单元格L2中。

.Range("L2").Value = c.Value

.Range(“ L2”)。Value = c.Value

I revised the code, so it includes the equal sign and quote marks, just as the manually-entered formula would have.

我修改了代码，使其包含了等号和引号，就像手动输入的公式一样。

.Range("L2").Value = "=""="" & " & Chr(34) & c.Value & Chr(34)

.Range(“ L2”)。Value =“ =”“ =”“＆”＆Chr(34)＆c.Value＆Chr(34)

Now, when you run the macro, only the exact matches for a sales rep name will appear on the list (upper and lower case will not affect the results).

现在，当您运行宏时，列表中将仅显示与销售代表名称完全匹配的名称(大写和小写不会影响结果)。

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

To test the Advance Filter macros, you can download the sample files from the Contextures website.

若要测试高级筛选器宏，可以从Contextures网站下载示例文件。

On the Sample Excel Files page, go to the Filters section. Then, look for these files:

在“ 示例Excel文件”页上 ，转到“ 过滤器”部分。 然后，查找以下文件：

• FL0013 - Create New Sheets from Filtered List
  FL0013-从过滤列表创建新图纸
• FL0004 - Create New Sheets from List
  FL0004-从列表创建新表

进阶筛选影片 (Advanced Filter Video)

To see the steps for a manual Advanced Filter to a different worksheet, you can watch this short Excel video tutorial.

要查看将“高级筛选器”手动转换到其他工作表的步骤，可以观看此简短的Excel视频教程。

演示地址