MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Advanced Filter - Help with Code

  Asked By: Lillian    Date: Mar 22    Category: MS Office    Views: 1431

I got this code from "Contextures" website.

Can someone please help me to change it so that the filter is on
column "F" and not "C". i also want the filter to start from F26.

Otherwise I want to use the code so that each filtered name in
column F has a new sheet.

Option Explicit

Sub ExtractReps()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("Sheet1")
Set rng = Range("Database")

'extract a list of Sales Reps
ws1.Columns("C:C").Copy _
ws1.Columns("L:L").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("J1"), Unique:=True
r = Cells(Rows.Count, "J").End(xlUp).Row

'set up Criteria Area
Range("L1").Value = Range("C1").Value

For Each c In Range("J2:J" & r)
'add the rep name to the criteria area
ws1.Range("L2").Value = c.Value
'add new sheet (if required)
'and run advanced filter
If WksExists(c.Value) Then
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=Sheets(c.Value).Range("A1"), _
Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=wsNew.Range("A1"), _
End If
End Sub
Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function



6 Answers Found

Answer #1    Answered By: Blake Smith     Answered On: Mar 22

First you would change:
ws1.Columns("C:C").Copy Destination:=Range("L1")
to this:
ws1.Columns("F:F").Copy Destination:=Range("L1")

And change:
For Each c In Range("J2:J" & r)
to this:
For Each c In Range("J26:J" & r)

Assuming your data starts in cell F26.

Answer #2    Answered By: Ryder Anderson     Answered On: Mar 22

Many Thanks ... I will try this and let you know

Answer #3    Answered By: Angelica Ramos     Answered On: Mar 22

I get an error -

Method 'Range of object'_Global' failed.

This occurs on the following lines ...

Set rng = Range("Database")

If I delete this line then I get another error ...

Method 'Range of object'_Worksheet failed failed.

This occurrs here ...

wsNew.Name = c.Value

I look forward to your reply.

Answer #4    Answered By: Lonnie Rogers     Answered On: Mar 22

I don't understand what your questions have to do with my response.

Do you have a range named "Database" on your worksheet? If not, then
your code will fail.

Have you stepped through the code to see where it needs to be
customized? It probably won't work as written unless your sheet is
formatted exactly the same as how the code expects it.

Answer #5    Answered By: Hubba Akhtar     Answered On: Mar 22

Thanks for your reply ... I have set a range named "Database" but its the second

i.e. 'Method 'Range of object'_Worksheet failed ' which is causing problems.

If you can't help thats ok.

Answer #6    Answered By: Sumitra 2004     Answered On: Mar 22

Isn't 'Database" a reserved word?
Just Name it somethind slightly different; "Database_MyDB"

Didn't find what you were looking for? Find more on Advanced Filter - Help with Code Or get search suggestion and latest updates.