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!