Filter Excel Data Onto Multiple Sheets

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.

filtersheetrep05

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.

filtersheetrep01

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.

filtersheetrep03

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.

filtersheetrep02

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"

filtersheetrep04

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

filtersheetrep06

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)

filtersheetrep07

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

______________

You may also like...

8 Responses

  1. Martin says:

    Or you could use the Excel add-in ASAP Utilities, which does the job for you. :)

  2. Sara says:

    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

  3. John says:

    In Sub ExtractReps() code you have a line; Set rng = Range(“Database”)

    Where is “Database” assigned a value?

  4. Denise says:

    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!

  5. Carlos says:

    Thanks a lot. This script is very useful. I was searching exactly for this!

  6. Shakthi says:

    Hi,

    Thanks a lot, you code helped me a lot.

  7. Larry says:

    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.

Leave a Reply to Martin Cancel reply

Your email address will not be published. Required fields are marked *