星期三, 二月 22, 2012

Filter Excel Data Onto Multiple Sheets

From Contexture.com

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





______________


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


没有评论: