Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel VBA Filter resulting in Method Autofilter of Object Range Failed

Posted on 2013-12-18
7
Medium Priority
?
977 Views
Last Modified: 2013-12-19
I have written a VBA command to use a range value in the tab where the DataTable resides as a filter.

I want to perform the filter on the table and then copy the range of data to a another tab.

The Data to be used as a filter is in cells A1:A2 (A1 is the field heading)

The DataTable begins in cell A7 and the length of the table can vary depending on how many records are returned from the data source.

I think what is happening is that if a filter has been previously done, the DataTable is still reflecting the previous filter.

What is syntax to display the whole data table before performing the new filter?

I have attached a screen shot of the DataTable I want to apply the filter to.

Here is the code I am using:

'
'
'   Select Data from MeditechData tab and Name Range
'
'

   
    Sheets("MeditechData").Select
    ActiveWorkbook.Names("DataRange").Delete
    Sheets("MeditechData").Select
    Range("A5").CurrentRegion.Select
   
    ActiveWorkbook.Names.Add Name:="DataRange", RefersTo:=Selection
   
'
'
'   Apply Filter
'
'

    Selection.AutoFilter
    Range("DataRange").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("A1:A2"), Unique:=False
   
'
'
'   Select Filtered Data and copy to clipboard
'
'
    Range("A5").CurrentRegion.Select
    Selection.Copy
   
 '


Thanks

Glen
Denial-FilterDara.jpg
0
Comment
Question by:GPSPOW
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39728274
Can you upload a small portion of your file for testing? You may fake the values.
0
 

Author Comment

by:GPSPOW
ID: 39728284
Here is the file with the data value copied to save space.

Thanks

Glen
Test-Denials.xlsm
0
 
LVL 81

Expert Comment

by:byundt
ID: 39728287
I rewrote your macro to copy the data to Sheet2. I used ShowAllData to undo the Advanced Filter.

Sub FilterIt()
Dim nm As Name
Dim addr As String
'
'   Select Data from MeditechData tab and Name Range
With Sheets("MeditechData")
    addr = .Range("A5").CurrentRegion.Address
    On Error Resume Next
    .ShowAllData
    Set nm = ActiveWorkbook.Names("DataRange")
    On Error GoTo 0
    If nm Is Nothing Then
        Set nm = ActiveWorkbook.Names.Add(Name:="DataRange", RefersTo:="='" & .Name & "'!" & addr)
    Else
        nm.RefersTo = "='" & .Name & "'!" & addr
    End If
    
'   Apply Filter
    .Range("DataRange").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        .Range("A1:A2"), Unique:=False

'   Select Filtered Data and copy to clipboard
    nm.RefersToRange.Copy
End With

Worksheets("Sheet2").Range("A5").PasteSpecial xlPasteValues
End Sub

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 39728296
In your actual macro & workbook, I modified the code as follows:

Sub FilterData()
'
' Filer Data and Copy to PT_Lookup tab

'
'
'   Unhide Columns on PT_Lookup tab
'
'


    Sheets("Pt_Lookup").Select
    
    Columns("D:N").Select
    'Range("D4").Activate
    Selection.EntireColumn.Hidden = False
    
'
'
'   Clear Contents in PT_Lookup area
'
'

    
    Range("E7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.ClearContents
'
'
'   Select Data from MeditechData tab and Name Range
'
'

    
    Sheets("MeditechData").Select
    ActiveWorkbook.Names("DataRange").Delete
    Sheets("MeditechData").Select
    Range("A5").CurrentRegion.Select
    
    ActiveWorkbook.Names.Add Name:="DataRange", RefersTo:="=" & Selection.Address
    
'
'
'   Apply Filter
'
'

    If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
    Range("DataRange").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("A1:A2"), Unique:=False
    
'
'
'   Select Filtered Data and copy to clipboard
'
'
    Range("A5").CurrentRegion.Select
    Selection.Copy
    
 '
 '
 '  Select PT_Lookup tab and paste clipboard ccontents
 '
 '
    Sheets("Pt_Lookup").Select
    Range("E7").Select
    
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  '
  '
  ' Hide PT_Lookup Columns
  '
  '
    Columns("E:M").Select
    'Range("E4").Activate
    Selection.EntireColumn.Hidden = True
End Sub

Open in new window

0
 

Author Closing Comment

by:GPSPOW
ID: 39728327
The first solution did not work.  The second one was perfect.

Thank you very much

Glen
0
 
LVL 81

Expert Comment

by:byundt
ID: 39728390
The first solution did not work.  The second one was perfect.
The first suggestion followed good professional practices, did what you requested in your question, and worked in tests on my sample workbook. It required modification to do the full job in your actual workbook, however, because your macro was also doing things besides the requested named range change, filter & copy.

The second solution worked without needing change in your actual workbook, but it started with a recorded macro and therefore embodied a number of poor coding practices (such as selecting ranges and worksheets, needlessly deleting named ranges).

I've rewritten the second macro following more professional coding practices. It will work unchanged in your posted workbook.
Sub FilterData2()
Dim rgDest As Range
Dim nm As Name
Dim addr As String
Application.ScreenUpdating = False

' Filter Data and Copy to PT_Lookup tab

'           Unhide Columns on PT_Lookup tab
    With Worksheets("Pt_Lookup")
        .Range("D:N").EntireColumn.Hidden = False
'
'           Clear Contents in PT_Lookup area
        Set rgDest = .Range("E7")
        Set rgDest = Range(rgDest, rgDest.End(xlDown))
        Set rgDest = Range(rgDest, rgDest.End(xlToRight))
        rgDest.ClearContents
    End With

'           Select Data from MeditechData tab and Name Range
    With Worksheets("MeditechData")
        addr = "='" & .Name & "'!" & .Range("A5").CurrentRegion.Address
        On Error Resume Next
        Set nm = ActiveWorkbook.Names("DataRange")
        If nm Is Nothing Then Set nm = ActiveWorkbook.Names.Add(Name:="DataRange", RefersTo:=addr)
        On Error GoTo 0
        nm.RefersTo = addr
    
'           Apply Filter
        If .FilterMode Then .ShowAllData
        .Range("DataRange").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=.Range("A1:A2"), Unique:=False
        
'           Select Filtered Data and copy to clipboard
        .Range("A5").CurrentRegion.Copy
    End With
 
 '  Select PT_Lookup tab and paste clipboard ccontents
    rgDest.Cells(1, 1).PasteSpecial Paste:=xlPasteValues
  
  ' Hide PT_Lookup Columns
    rgDest.Worksheet.Range("E:M").EntireColumn.Hidden = True
End Sub

Open in new window

0
 

Author Comment

by:GPSPOW
ID: 39729222
Thanks

I will review the code and implement it for my user.

Glen
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question