how to use filter, vba and named ranges

I am having problems trying to use auto filter with named ranges, it doesn't filter using the named range wpfilter, any ideas?

Sub Copy_Resource_Data()

Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
Dim cID As Range, wp As Range, wpRange As Range, y As Range, yRange As Range
Dim Filter As Range
Dim CountryLastRow As Long



'This is where the data will end up
Set ws1 = Sheets("Resource")

' This is where we take the filters from
Set ws2 = Sheets("Bid Summary")

'This is where the non resource build will go
Set ws3 = Sheets("Non Resource")

'This is where the source data is held
Set ws4 = Sheets("Temp_All_eDRP_Data")

'This is where the lookups are on
Set ws5 = Sheets("LookUps")

'This sets the range D11 as the Contract ID
Set cID = Worksheets("Bid Summary").Range("D11")

'Now we set up all the work package ranges
Set wpRange = ws5.Range("WPFilter")

VwpRange = wpRange.Value

'Finally we set up the years
'Set yRange = Worksheets("LookUps").Range("K2:K16")
Set y = Worksheets("LookUps").Range("K2")

'Filter ranges on the resource tab
Set Filter = Worksheets("Temp_All_eDRP_Data").Range("A1:Y1")

With Filter
    .AutoFilter field:=1, Criteria1:=cID, Operator:=xlAnd
    .AutoFilter field:=13, Criteria1:=VwpRange
    .AutoFilter field:=25, Criteria1:="GSS"
End With

    Sheets("Temp_All_eDRP_Data").Select
    
End Sub

Open in new window

bryanscott53Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
use:
Criteria1:=Range("WPFilter")
bryanscott53Author Commented:
Thanks Crystal, this doesn't work, when the macro runs there are no rows filtered
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
were there rows when you started?

Perhaps you can attach a workbook with sample information and I can see what is going on?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
it seems that range is specified as criteria, not as the range to filter ... but without looking, it is hard to say
bryanscott53Author Commented:
I will attach sample later, there are a lot of rows in the file that should match the criteria, appreciate your help
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
ok.  It is getting late here ... so if it is too much later, I won't be able to look until tomorrow
bryanscott53Author Commented:
No worries at all appreciate any help at all
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
someone else may be able to jump in though :) I will look again in the morning.
bryanscott53Author Commented:
ok, here is my sample file. thanks for your help
SampleData.xlsb
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
got it -- looking now ~
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
add this to the top of the module:

Option Explicit

When you compile, more errors will be picked up.

wk5 is not dimensioned

VwpRange and wp1LastRow were not dimensioned

Set ws3 = Sheets("Non Resource"): there is no sheet called that so I added it

Lookups (wk5) sheet does not have a range called WPrngFilter

this:
VwpRange = wpRange.Value
does not work if the range is more than a single cell

rename Filter to rngFilter since using Filter as a name may create confusion with Excel.

To better see where error are when you run,
add an error handler.  So the top of the code will be:
   On Error GoTo Proc_Err

   Dim ws1 As Worksheet _
      , ws2 As Worksheet _
      , ws3 As Worksheet _
      , ws4 As Worksheet _
      , ws5 As Worksheet
   Dim cID As Range _
      , wp As Range _
      , wpRange As Range _
      , y As Range _
      , yFilter As Range _
      , rngFilter As Range
      
   Dim CountryLastRow As Long _
      , VwpRange _
      , wp1LastRow As Long

Open in new window

and the bottom will be:
Proc_Exit:
   On Error Resume Next
   'release object variables
   Set cID = Nothing
   Set wp = Nothing
   Set y = Nothing
   Set yFilter = Nothing
   Set rngFilter = Nothing 'renamed from Filter
   Set ws1 = Nothing
   Set ws2 = Nothing
   Set ws3 = Nothing
   Set ws4 = Nothing
   Set ws5 = Nothing
   Exit Sub
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   Copy_Resource_Data"
   Resume Proc_Exit
   Resume

End Sub

Open in new window

'~~~~~~~~~ Compile ~~~~~~~~~
 
Whenever you change code, references, or switch versions, you should always compile before executing.
 
from the menu in a VBE (module) window: Debug, Compile
 
fix any errors on the highlighted lines
 
keep compiling until nothing happens (this is good!)

___________________________________
If you get an error when you run:

press Ctrl-C immediately to copy the error message and paste into a message back to me (that does not always work, in which case you have to type what it says)

Press Ctrl-Break and then choose Debug.  This will take you to the error handler code.  (you may have to OK the message first)

right-click on the statement at the bottom that says Resume
and choose -- Set Next Statement

press F8 to execute a single-step at a time and that will take you back to the line where the error happened (which may or may not be the statement to change)
______________________________

However, if I know the logic you want to implement, I can also help you better ;) ~
Perhaps you can describe it to me? thank you
bryanscott53Author Commented:
Sorry its so long in getting back I was called onto something else. I have made the changes you have listed above, I also removed the lines around setting the years etc. I get an error (see image)

screen capture of the error
I am trying to filter the ws4 = Sheets("Temp_All_eDRP_Data") based on the criteria that is on the lookups sheet (column K) this is the named range WPFilter

Thanks for your help with this and sticking with it, appreciate it
SampleData_v2.xlsb
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome

have a training call now ~ will look when I am done
bryanscott53Author Commented:
Hi board, anyone able to help me?
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
sorry I lost the thread ...

when you get the error message, we need to see the code that is causing it ...

Press Ctrl-Break when you get the error message and choose Debug.  This will take you to the error handler code.  (you may have to OK the message first)

right-click on the statement at the bottom that says Resume
and choose -- Set Next Statement

press F8 to execute a single-step at a time and that will take you back to the line where the error happened (which may or may not be the statement to change)

what does it say?  We need to also see what is above this code.

However, I see what might be the problem: ws4 = Sheets("Temp_All_eDRP_Data")  does not specify what workbook it is in.  Also, this should be SET ws4 = ...

... really though just guessing what might be wrong.  Please post the code as it is now if you have made any changes, thank you
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
oh! I see you attached the workbook ... sorry, I missed that ~ if you still want help, post back
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
I see this:
Set RngFilter = Worksheets("Temp_All_eDRP_Data").Range("A1:Y1")

but there seem to be more columns (and rows)  ... what is the range you want to filter?

very sorry for dropping the ball before ... caught up better now ;)

when you post back, I get a notification so I can come back if you still want help ... occurred to me that perhaps you already figured it out and I didn't want to waste my time if that is the case.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.