There is a sample Excel file on the Contextures website that has a list of orders, and sales rep names. You can click a button, and a sheet is created for each sales rep, with that person's orders.
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.
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.
In the screen shot below, the first name from the list, Gill, has been entered into the criteria range.
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.
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.
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:
="=" & "Gill"
Fix the Problem in the VBA Code
In the original Excel VBA code, the sales rep's name is entered into cell L2.
.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)
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.
On the Sample Excel Files page, go to the Filters section. Then, look for these files:
- FL0013 - Create New Sheets from Filtered List
- FL0004 - Create New Sheets from List
Advanced Filter Video
To see the steps for a manual Advanced Filter to a different worksheet, you can watch this short Excel video tutorial.
Or watch on YouTube: Automatically Copy Excel Data to Other Sheet
______________
RSS Footer: Thank you for subscribing to the RSS feed for Contextures Blog .
You can read the full article here: Filter Excel Data Onto Multiple Sheets
Please visit the Contextures page on Facebook
没有评论:
发表评论