There is a sample Excel file on my 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.
Excel File – Version 2
There is also 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 the Excel Macro 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.
What Caused the Problem?
Why did this problem 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.
How to Get Exact Matches Only
To get an exact match only, you can change the criteria cell, so it uses an equal sign before the name.
To do this manually, you would enter the following 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.
There is a full written transcript below the video.
More Excel Advanced Filter Examples
Send Excel Data to Another Sheet
Advanced Filter Criteria Slicers
Video Transcript
In Excel, you can use an Advanced Filter to pull data from a table and put it somewhere else in the workbook, either on the same sheet or a different sheet.
In this example, we have a list of orders and we would like to pull the orders with the highest totals and put them on a different sheet in the workbook. I would like to get the date and the customer for each of the high priced orders.
Criteria Area
Back on the Orders sheet, I’ve started a criteria area here. I’m using total, which is the column where the order amount is in the main table. That’s the criteria heading, and below that I’m going to enter my criterion, which is greater than 1500.
I only want orders where the total is greater than 1500. I’m not putting dollar signs in here. It’s just the number that I wanted to check.
We want to pull orders onto a different worksheet. In this case, we have to start on this destination worksheet, and I want to stay away from the cells near the heading. So, I’m just going to click a cell somewhere in an empty area of the worksheet.
Start Filter
To start the filter on the ribbon, I’ll go to the Data tab and click Advanced. In the Advanced Filter dialog, I’m going to select Copy to Another Location.
The List range is my main table. So, I’ll click here, then go to the Orders sheet and select the entire table, including the headings. That’s entered as the list range.
My Criteria range, I’ll click in that cell. Go back to the Orders sheet where I created my criteria range and select the heading cell and the criteria cell below that.
For CopyTo, this is where I want the data to end up. I’ll click here and select my two heading cells on the TopOrders sheet and I’ll click OK, and there are the 3 orders where the total is higher than 1500.
If we look back, we can see 1, 2, 3 orders, and those have been copied automatically to the other worksheet using an Advanced Filter.
______________
Or you could use the Excel add-in ASAP Utilities, which does the job for you. 🙂
Could you enhance your code to send a mail to the sales rep of the sales he made…
means he will get a copy of the sheet we created under his name… is it possible, please mail me
In Sub ExtractReps() code you have a line; Set rng = Range(“Database”)
Where is “Database” assigned a value?
Database is a range of cells – go to the Formulas tab, under Name Manager and you can update.
I am not a developer and have been searching for a way to implement this functionality for several days, so THANK YOU FOR POSTING THE TEMPLATE! I am modifying it a bit for my needs and have just one question:
I have many more columns that need to be copied to the other tabs whenever the macro runs (your template copies just A-G). How can I expand the range in to code so that columns A through AT are included in the subsequent tabs?
Thank you again!
Thanks a lot. This script is very useful. I was searching exactly for this!
Hi,
Thanks a lot, you code helped me a lot.
What if you had multiple reps in one cell? Can your copy be modified to copy that record to both rep sheets? Example: Morgan-Gill. Then Morgan will show that record as will Gill.