Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering results
I'm not quite sure wot u mean, this is the code the Supplier tab ... ca u elaborate from this ? I've got the Supplier macro running from button and want another button in the customer tab ... Sub Split_Supplier_Codes() Dim ws1 As Worksheet Dim WSNew As Worksheet Dim rng As Range Dim r As Integer Dim c As Range Set ws1 = Sheets("Supplier") Set rng = Range("Database") 'extract a list of Code Nos ws1.Columns("A:A").Copy _ Destination:=Range("Z1") ws1.Columns("Z:Z").AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=Range("X1"), Unique:=True r = Cells(Rows.Count, "X").End(xlUp).Row 'set up Criteria Area Range("Z1").Value = Range("A1").Value For Each c In Range("X2:X" & r) 'add the code no to the criteria area ws1.Range("Z2").Value = c.Value 'add new sheet (if required) 'and run advanced filter If WksExists(c.Value) Then Sheets(c.Value).Cells.Clear rng.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Supplier").Range("Z1:Z2"), _ CopyToRange:=Sheets(c.Value).Range("A1"), _ Unique:=False Else Set WSNew = Sheets.Add WSNew.Move After:=Worksheets(Worksheets.Count) WSNew.Name = c.Value rng.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Supplier").Range("Z1:Z2"), _ CopyToRange:=WSNew.Range("A1"), _ Unique:=True End If Next ws1.Select ws1.Columns("Y:Z").Delete End Sub Function WksExists(wksName As String) As Boolean On Error Resume Next WksExists = CBool(Len(Worksheets(wksName).Name) 0) End Function Cheers :confused -- GazM ----------------------------------------------------------------------- GazMo's Profile: http://www.excelforum.com/member.php...fo&userid=1461 View this thread: http://www.excelforum.com/showthread.php?threadid=26926 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering results
Hi GazMo My example will filter on the first column of the range (in my example is this column A because my range only have one column) If your range is bigger like A1:G100 and you want to filter on column B you must change this line rng.Columns(1).AdvancedFilter _ to rng.Columns(2).AdvancedFilter _ My macro use the LastRow function to find the last row with data if the sheet exist. The new data will be paste below the existing data -- Regards Ron de Bruin http://www.rondebruin.nl "GazMo" wrote in message ... I'm not quite sure wot u mean, this is the code the Supplier tab ... can u elaborate from this ? I've got the Supplier macro running from a button and want another button in the customer tab ... Sub Split_Supplier_Codes() Dim ws1 As Worksheet Dim WSNew As Worksheet Dim rng As Range Dim r As Integer Dim c As Range Set ws1 = Sheets("Supplier") Set rng = Range("Database") 'extract a list of Code Nos ws1.Columns("A:A").Copy _ Destination:=Range("Z1") ws1.Columns("Z:Z").AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=Range("X1"), Unique:=True r = Cells(Rows.Count, "X").End(xlUp).Row 'set up Criteria Area Range("Z1").Value = Range("A1").Value For Each c In Range("X2:X" & r) 'add the code no to the criteria area ws1.Range("Z2").Value = c.Value 'add new sheet (if required) 'and run advanced filter If WksExists(c.Value) Then Sheets(c.Value).Cells.Clear rng.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Supplier").Range("Z1:Z2"), _ CopyToRange:=Sheets(c.Value).Range("A1"), _ Unique:=False Else Set WSNew = Sheets.Add WSNew.Move After:=Worksheets(Worksheets.Count) WSNew.Name = c.Value rng.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Supplier").Range("Z1:Z2"), _ CopyToRange:=WSNew.Range("A1"), _ Unique:=True End If Next ws1.Select ws1.Columns("Y:Z").Delete End Sub Function WksExists(wksName As String) As Boolean On Error Resume Next WksExists = CBool(Len(Worksheets(wksName).Name) 0) End Function Cheers -- GazMo ------------------------------------------------------------------------ GazMo's Profile: http://www.excelforum.com/member.php...o&userid=14610 View this thread: http://www.excelforum.com/showthread...hreadid=269267 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering results
Hi GazMo
Give me your filter range and the filter column and I will make a new example -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi GazMo My example will filter on the first column of the range (in my example is this column A because my range only have one column) If your range is bigger like A1:G100 and you want to filter on column B you must change this line rng.Columns(1).AdvancedFilter _ to rng.Columns(2).AdvancedFilter _ My macro use the LastRow function to find the last row with data if the sheet exist. The new data will be paste below the existing data -- Regards Ron de Bruin http://www.rondebruin.nl "GazMo" wrote in message ... I'm not quite sure wot u mean, this is the code the Supplier tab ... can u elaborate from this ? I've got the Supplier macro running from a button and want another button in the customer tab ... Sub Split_Supplier_Codes() Dim ws1 As Worksheet Dim WSNew As Worksheet Dim rng As Range Dim r As Integer Dim c As Range Set ws1 = Sheets("Supplier") Set rng = Range("Database") 'extract a list of Code Nos ws1.Columns("A:A").Copy _ Destination:=Range("Z1") ws1.Columns("Z:Z").AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=Range("X1"), Unique:=True r = Cells(Rows.Count, "X").End(xlUp).Row 'set up Criteria Area Range("Z1").Value = Range("A1").Value For Each c In Range("X2:X" & r) 'add the code no to the criteria area ws1.Range("Z2").Value = c.Value 'add new sheet (if required) 'and run advanced filter If WksExists(c.Value) Then Sheets(c.Value).Cells.Clear rng.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Supplier").Range("Z1:Z2"), _ CopyToRange:=Sheets(c.Value).Range("A1"), _ Unique:=False Else Set WSNew = Sheets.Add WSNew.Move After:=Worksheets(Worksheets.Count) WSNew.Name = c.Value rng.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Supplier").Range("Z1:Z2"), _ CopyToRange:=WSNew.Range("A1"), _ Unique:=True End If Next ws1.Select ws1.Columns("Y:Z").Delete End Sub Function WksExists(wksName As String) As Boolean On Error Resume Next WksExists = CBool(Len(Worksheets(wksName).Name) 0) End Function Cheers -- GazMo ------------------------------------------------------------------------ GazMo's Profile: http://www.excelforum.com/member.php...o&userid=14610 View this thread: http://www.excelforum.com/showthread...hreadid=269267 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FILTERING RESULTS | Excel Worksheet Functions | |||
Filtering results | Excel Discussion (Misc queries) | |||
filtering results to another sheet | Excel Discussion (Misc queries) | |||
sorting and filtering results | Excel Worksheet Functions | |||
Filtering results | Excel Programming |