Autofilter not resulting in an expected Manner with VBA coding

[NimishK]

Hello

Somehow the below coding does not result for exact Autofilter execution

Private Sub AFM_NotFunctioning_As Per_Expectation ()

Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet
Dim lastUsdRowWks2 AS Long, lastUsdRowWks1 AS Long
Dim MangrsNameArr As Variant

Set Wks1 = Worksheets("MANAGER NAMES")
Set wks2 = Worksheets("PYMNTS")

lastUsdRowWks2 = wks2.Range("A" & Rows.Count).End(xlUp).Row
lastUsdRowWks1 = wks1.Range("A" & Rows.Count).End(xlUp).Row

MangrsNameArr = Wks1.Range("A2:A11").Value
 
wks2.Range("A1:K1").AutoFilter Field:=4, Criteria1:=MangrsNameArr, Operator:=xlFilterValues

End Sub

The Above Syntax only shows one record of only one manager in Wks2 and not the records of other managers as per the List range in column A2: A11 of wks1
The Manager Names are also there in wks2 colunm D to match the Criteria List Name of Managers as per Wks1

Kindly Help me to achieve what i desire

Thanks
NimishK

 

[gordsky]

Hello

Somehow the below coding does not result for exact Autofilter execution

Private Sub AFM_NotFunctioning_As Per_Expectation ()

Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet
Dim lastUsdRowWks2 AS Long, lastUsdRowWks1 AS Long
Dim MangrsNameArr As Variant

Set Wks1 = Worksheets("MANAGER NAMES")
Set wks2 = Worksheets("PYMNTS")

lastUsdRowWks2 = wks2.Range("A" & Rows.Count).End(xlUp).Row
lastUsdRowWks1 = wks1.Range("A" & Rows.Count).End(xlUp).Row

MangrsNameArr = Wks1.Range("A2:A11").Value
 
wks2.Range("A1:K1").AutoFilter Field:=4, Criteria1:=MangrsNameArr, Operator:=xlFilterValues

End Sub

The Above Syntax only shows one record of only one manager in Wks2 and not the records of other managers as per the List range in column A2: A11 of wks1
The Manager Names are also there in wks2 colunm D to match the Criteria List Name of Managers as per Wks1

Kindly Help me to achieve what i desire

Thanks
NimishK

you are asking the criteria to search multiple names in an "array" style. I think you need to change this line

MangrsNameArr = Wks1.Range("A2:A11").Value

to this

MangrsNameArr = Application.Transpose(Wks1.Range("A2:A11"))

 

[NimishK]

to this
VBA Code:
MangrsNameArr = Application.Transpose(Wks1.Range("A2:A11"))

Gordsky
Although it executed
Unfortunately it displays only few mangers out of the MangrsNameArr . Bit surprised it did not take all the Managers name out of the Range defined
What is that I've done wrong in the AutoFilter Syntax. Because all the Selected Managers as per list have recd pymts. ?

NimishK

 

[gordsky]

Gordsky
Although it executed
Unfortunately it displays only few mangers out of the MangrsNameArr . Bit surprised it did not take all the Managers name
What is that I've done wrong in the AutoFilter Syntax. Because all the Managers have recd pymts. ?

NimishK

what is the exact cell range of your managers

 

[NimishK]

what is the exact cell range of your managers

Set Wks1 = Worksheets("MANAGER NAMES") Full List Range A2:A11
Set wks2 = Worksheets("PYMNTS") Full Details of Staff members are there too with the List of Managers are there in ColumnD of Sheet Pymnts

 

[gordsky]

Set Wks1 = Worksheets("MANAGER NAMES") Full List Range A2:A11
Set wks2 = Worksheets("PYMNTS") Full Details of Staff members are there too where the List of Managers are there in ColumnD of Sheet Pymnts

the code works fine for me, are you able to attach a sample workbook

 

[NimishK]

the code works fine for me, are you able to attach a sample workbook

Unfortunately Company Details. So would not be able to upload the same

What are the like chances that i am unable to operate NOT smoothly

 

[gordsky]

Unfortunately Company Details. So would not be able to upload the same

What are the like chances that i am unable to operate NOT smoothly

ok so can you knock up a dummy workbook with made up info to illustrate what you are doing.
also you have these lines but then dont use them anywhere

lastUsdRowWks2 = wks2.Range("A" & Rows.Count).End(xlUp).Row
lastUsdRowWks1 = wks1.Range("A" & Rows.Count).End(xlUp).Row

 

[NimishK]

lastUsdRowWks2 = wks2.Range("A" & Rows.Count).End(xlUp).Row
lastUsdRowWks1 = wks1.Range("A" & Rows.Count).End(xlUp).Row

To Display the records uptil last row used but i directly used Wks1.Range("A2:A11")......

 

[gordsky]

Unfortunately Company Details. So would not be able to upload the same

What are the like chances that i am unable to operate NOT smoothly

going back to your original code what happens if you try this.

change

MangrsNameArr = Wks1.Range("A2:A11").Value

to

set MangrsNameArr = Wks1.Range("A2:A11")

then add below

MyMngrs = MangrsNameArr.value

and then change the criteria to be

application.transpose(MyMngrs)