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

 

[NimishK]

application.transpose(MyMngrs)

It takes the First Record only of From Managers List Sheet
you may please take your time.

Do you think there may grammtical mistakes in list of names if yes let me check and revert back

 

[gordsky]

It takes the First Record only of From Managers List Sheet
you may please take your time.

Do you think there may grammtical mistakes in list of names if yes let me check and revert back

it is possible I knocked up a very simple book based on your sheets and it works fine. Im wondering if there are additional spaces in the names either at the start or the end. You could check and remove them by using the following code. Obviously change the range to be that of the names on your activesheets

ActiveSheet.[A1:c200] = ActiveSheet.[INDEX(TRIM(A1:c200),)]

ive tested the below code on three different datasets and its worked on them all so the issue must lie in your data set of names on sht2 not matching your list of names on sht1

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:=Application.Transpose(MangrsNameArr), Operator:=xlFilterValues

 

[NimishK]

How about trying this way

Diim MangrsNameArr (1 To 15) As String

 

[NimishK]

net got hanged for #13


Dim ManagersNameArr (1 To 10) As String

ManagersNameArr(1) = "Manager1"
ManagersNameArr(2) = "Manager2"
ManagersNameArr(3) = "Manager3"
ManagersNameArr(4) = "Manager3"
ManagersNameArr(4) .... and so on uptil 10

Suppose if we go with above reference as the list is short of 10 managers

Im wondering if there are additional spaces in the names either at the start or the end. You could check and remove them by using the following code. Obviously change the range to be that of the names on your activesheets

then what could be additonal in coding for Array traanspose affect
So that i can TRIM the above Strings

 

[gordsky]

net got hanged for #13


Dim ManagersNameArr (1 To 10) As String

ManagersNameArr(1) = "Manager1"
ManagersNameArr(2) = "Manager2"
ManagersNameArr(3) = "Manager3"
ManagersNameArr(4) = "Manager3"
ManagersNameArr(4) .... and so on uptil 10

Suppose if we go with above reference as the list is short of 10 managers

then what could be additonal in coding for Array traanspose affect
So that i can TRIM the above Strings

i would be more inclined to fix the dataset names you have already. Did you run the trim code i posted above on both lists?

 

[NimishK]

VBA Code:
ActiveSheet.[A1:c200] = ActiveSheet.[INDEX(TRIM(A1:c200),)]

Indeed it worked and Spaces were the issue

 

[gordsky]

Indeed it worked and Spaces were the issue

glad we got it sorted, is the filter working fine now. If so please mark as resolved

 

[NimishK]

Gordsky

Finally got sorted and Thank you so much for spending your valuable time and indeed a great help for resolving the same.

Thank you so much

NimishK

 

[Peter_SSs]

@NimishK
Please mark the post that has the best answer for you, not your own post that simply says it is resolved. (I have removed that mark)

 

[NimishK]

Please mark the post that has the best answer for you, not your own post that simply says it is resolved. (I have removed that mark)

Agreed Sir and implemented