+ Reply to Thread
Results 1 to 4 of 4

Autofilter based upon a cell value in another workbook

  1. #1
    Registered User
    Join Date
    03-08-2012
    Location
    Notts
    MS-Off Ver
    Excel 2010
    Posts
    71

    Question Autofilter based upon a cell value in another workbook

    Can anyone advise me on the code I would need to add to the following line of code in order for it to use cell C9 from another workbook as the basis of the autofilter?

    Selection.AutoFilter Field:=13, Criteria1:="=*" & Sheets("Responsible Work to List").Range("C9") & "*"
    Currently the line of code works when using C9 on the "Responsible Work to List" sheet within the current workbook.
    For purpose of example; the other workbook is named "Concern Tracker" and the sheet I want to to refer to cell C9 is on "Sheet1".

    Having a stab myself, would I need to add something along the lines of:

    Selection.AutoFilter Field:=13, Criteria1:="=*"workbooks("Concern Tracker") & Sheets("Responsible Work to List").Range("C9") & "*"
    ???

    Thanks in advance for any help!

  2. #2
    Registered User
    Join Date
    03-08-2012
    Location
    Notts
    MS-Off Ver
    Excel 2010
    Posts
    71

    Re: Autofilter based upon a cell value in another workbook

    I have just tried this:

    Selection.AutoFilter Field:=13, Criteria1:="=*" & Windows("Concern Tracker").Sheets("Responsible Work to List").Range("C9") & "*"
    ... but still no luck!
    Getting runtime error 438 - Object doesn't support property or method.


    Any ideas??

  3. #3
    Registered User
    Join Date
    03-08-2012
    Location
    Notts
    MS-Off Ver
    Excel 2010
    Posts
    71

    Re: Autofilter based upon a cell value in another workbook

    still having trouble with this if anyone has any ideas...?

  4. #4
    Registered User
    Join Date
    03-08-2012
    Location
    Notts
    MS-Off Ver
    Excel 2010
    Posts
    71

    Re: Autofilter based upon a cell value in another workbook

    For any that are interested, I have managed to solve my problem.

    I did it by setting the Range as a variable that could be used externally. See as follows:

    Dim extvalue As String
    extvalue = Workbooks("Concern Tracker.xls").Worksheets("Responsible Work to List").Range("C9").Value
    And then use in filters or code where needed:

    Selection.AutoFilter Field:=13, Criteria1:="=*" & extvalue & "*"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1