+ Reply to Thread
Results 1 to 2 of 2

VBA Dynamic Filter from different Workbook

  1. #1
    Registered User
    Join Date
    08-22-2016
    Location
    Germany
    MS-Off Ver
    2010
    Posts
    1

    VBA Dynamic Filter from different Workbook

    hi all,

    I am new to VBA and still in learning phase.

    I have a problem to get a filter running using dynamic data from a different workbook. The following describes what I want my macro to do:

    1. Active workbook1 is open
    2. macro in workbook1 opens a new workbook2
    3. macro should filter data in workbook2 using data from workbook1
    However, macro loses data after opeining workbook2 and doesnt filter as expected.

    CODE:
    Public thisworkbook As String

    Sub test2()
    thisworkbook = ActiveWorkbook.Name

    Dim arr As String
    arr = Workbooks(thisworkbook).Sheets("REPORTS").Cells(1, 22)
    Dim arr2 As String
    arr2 = Workbooks(thisworkbook).Sheets("REPORTS").Cells(1, 23)

    'up to here data is being extracted correctly
    End Sub

    Sub copy_masterdata16()
    Call test2


    ActiveWindow.Visible = True
    Workbooks.Open Filename:= _
    "\\xxx.xlsx" _
    , UpdateLinks:=0

    'msgbox shows no data for arr but thisworkbook still being Workbook1

    ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=27, Criteria1:= _
    arr2, Operator:=xlOr, Criteria2:="=SCPC"

    ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=8, Criteria1:=arr

    Hope you can help me.

    Thanks!
    Last edited by alex-hh; 08-22-2016 at 08:44 AM.

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166
    Hello alex-hh,

    Welcome to Excelforum. Be a part of large Excel community. Enjoy Learning.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Dynamic filter by numbers
    By martin.lasses in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-22-2016, 06:05 PM
  2. Macro to filter rows of one workbook based on list of values in another workbook.
    By jarrodprice in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-14-2015, 02:55 PM
  3. Filter and copy/overwrite to a new workbook and rename to unique filter value
    By gssc141 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-11-2015, 10:28 AM
  4. [SOLVED] Copying data from a closed workbook into an open workbook ignoring excel filter?
    By reach78 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 09-17-2013, 12:31 AM
  5. Open workbook, filter values, copy/paste into Active workbook.
    By niceguy21 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-10-2013, 12:17 PM
  6. creates a filter and copies to new workbook, want user to select filter
    By delboy2405 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-23-2012, 08:13 PM
  7. Creating Dynamic Folders/WorkBook from a Sheet with Dynamic Path.
    By e4excel in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 03-28-2011, 05:42 AM

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