Hello All,
I do order management for my company along with 7 others. We have 2 or 3 digit codes that represent each of our customers. Our PO's have 6 numerals, followed by the 2 or 3 digit customer code. We get daily reports that have hundreds of orders on them, The excel template we use is ordered like so:
Date Tracking PO
I am attempting to build a code that, when run, will detect the customer code within each string in PO field and then filter to only show me customer codes that belong to me specifically(accounts that i manage)
For instance: If the PO is 123456DWR-001, when initiated; the code should filter the PO field to only show me rows that have a PO containing "DWR". I have about 150 customer codes assigned to me. So since the report shows every ones customers it would be great if by the click of a button i could filter down to only my customers. So want this code to show me all rows of data that contain any of the "customer codes", which is a named range on another sheet. Once working i'm going to assign each person in my department a button within this workbook, so when the report is downloaded they can open it up, click their name and be shown only their customers.
I hope this isn't too long winded. Is this possible, with the code below does it seem like i'm going in the right direction?
This is what I have so far:
i found this code, and made a few changes at: http://blog.contextures.com/archives/2010/12/15/excel-autofilter-with-criteria-in-a-range/
I do order management for my company along with 7 others. We have 2 or 3 digit codes that represent each of our customers. Our PO's have 6 numerals, followed by the 2 or 3 digit customer code. We get daily reports that have hundreds of orders on them, The excel template we use is ordered like so:
Date Tracking PO
I am attempting to build a code that, when run, will detect the customer code within each string in PO field and then filter to only show me customer codes that belong to me specifically(accounts that i manage)
For instance: If the PO is 123456DWR-001, when initiated; the code should filter the PO field to only show me rows that have a PO containing "DWR". I have about 150 customer codes assigned to me. So since the report shows every ones customers it would be great if by the click of a button i could filter down to only my customers. So want this code to show me all rows of data that contain any of the "customer codes", which is a named range on another sheet. Once working i'm going to assign each person in my department a button within this workbook, so when the report is downloaded they can open it up, click their name and be shown only their customers.
I hope this isn't too long winded. Is this possible, with the code below does it seem like i'm going in the right direction?
This is what I have so far:
Code:
Sub Find_Dan()
Dim vCrit As Variant
Dim wsO As Worksheet
Dim wsL As Worksheet
Dim rngCrit As Range
Dim rngOrders As Range
Set wsO = Worksheets("Orders")
Set wsL = Worksheets("Dan")
Set rngOrders = wsO.Range("c:c").CurrentRegion
Set rngCrit = wsL.Range("$a$1:$a$148")
vCrit = rngCrit.Value
rngOrders.AutoFilter _
Field:=3, _
Criteria1:=Application.Transpose(vCrit), _
Operator:=xlFilterValues
End Sub
