Open de File with a sheet filtered according to a matriz

[TEIXEIRE]

Hello specialists,

I have a sheet called "Data", and there from row 4 and along, I have several collunms with data, like "Main Responsible" in AO, "Country" in AK and "SBU" in AL;
I am also have another sheet called "AuthUsers", where I've created the below matriz;

soo, I am looking for a VBA code, which everytime a person open the file, filter automatically these collumns according to the matriz below;

1 - In collumn "Country", filter two options if the matriz hace a comma;
2 - if blank, no filter;
3- if the name is not on collumns "User", no filter needed;

I usually use Application.UserName to have this feature, but I do not know if another way could be better;

could you help me on that ?

E.G. Sheet("AuthUsers")

User	Main Responsible	Country	SBU
Eloize Teixeira	Logistics	Brazil, Chile
Leonardo Contin	Customer Service	Brazil
Mariana Machado	Supply Chain	Brazil	ACC
Application.UserName	Data(AO:AO)	Data(AK:AK)	Data(AL:AL)

 

[Yongle]

Is it possible for Country to have MORE THAN 2 options ?

 

[TEIXEIRE]

Yes, it is possible !
or SBU too

 

[Yongle]

Advanced Filter can handle everything you require
I will update the thread with code for you to test within the next 24 hours

 

[TEIXEIRE]

thanks so much !

 

[Yongle]

In the meantime look at these links and make sure you understand how advanced filter works

Excel Advanced Filter - A Complete Guide with Examples

Excel Advanced Filter can be used to extract a list from a data base with predefined criteria. It gives a lot of control as compared regular filter.

trumpexcel.com

Excel Advanced Filter – how to create and use

The tutorial explains the basics of Excel Advanced Filter and how to use it to find data that meets one or more complex criteria.

www.ablebits.com

Excel Advanced Filter Intro - Clear Steps, Videos, Examples

How to use Advanced Filter in Excel with criteria, send data to different sheet, or filter in place. Short videos, clear examples, written steps, workbook

www.contextures.com

 

[TEIXEIRE]

hELLO, Thanks!
Actually I know how it works, but my question is how to filter by the user connected, cause each user has a different information to be filtered;
AND to make the list of authoruzation user easier, if there are to options, like country "Brazil, Chile", separete it with comma instead of duplicate the row with different country.

 

[Yongle]

Good
- in that case you will understand how this code works

Assumptions
- data values assumed to be in sheet Data starting in cell A1
- column A in sheet AuthUsers contains the SAME names as returned by Application.UserName
(you said you were familiar with Application.UserName in post#1)

What the code does
- finds match for ApplicationUserName in column A in sheet AuthUsers
- values in 3 adjacent columns are split (at each comma) and placed into 3 arrays (MainResp, Country, SBU)
- advanced filter criteria range created in columns CA to CC
-
- a line is created for each possible combination of all values in the 3 arrays

Notes
@@@ used to avoid problems splitting empty strings
The filter criteria may not be visible after filtering
- clear the filter and look at columns CA:CC

Sub FilterForUser()
    Dim wsData As Worksheet, r As Long, user As Range
    Dim MainResp As Variant, Country As Variant, SBU As Variant,  M As Variant, C As Variant, S As Variant, A As Variant
    Dim wF As WorksheetFunction: Set wF = Application.WorksheetFunction
    Set wsData = Sheets("Data"): wsData.Activate
    On Error Resume Next
    Set user = Sheets("AuthUsers").Cells(wF.Match(Application.UserName, Sheets("AuthUsers").Range("A:A"), 0), "A")
    If Err.Number <> 0 Then Exit Sub
    On Error GoTo 0
'get user filter values & create filter arrays
    MainResp = user.Offset(, 1): Country = user.Offset(, 2): SBU = user.Offset(, 3)
    If MainResp = "" Then MainResp = "@@@"
    If Country = "" Then Country = "@@@"
    If SBU = "" Then SBU = "@@@"
    MainResp = Split(MainResp, ",")
    Country = Split(Country, ",")
    SBU = Split(SBU, ",")
'create advanced filter criteria range in columns CA, CB and CC
    r = 1
    With wsData
        .Columns("CA:CC").ClearContents
        .Range("CA1:CC1").Value = Array(.Range("AO1"), .Range("AK1"), .Range("AL1"))        'headers must be exactly the same
        For Each M In MainResp
            For Each C In Country
                For Each S In SBU
                    M = Filter(M): C = Filter(C):   S = Filter(S)
                    If M & C & S <> "" Then
                        r = r + 1
                        .Cells(r, "CA").Resize(, 3).Value = Array(M, C, S)
                    End If
                Next S
            Next C
        Next M
        On Error Resume Next
        .ShowAllData
        .Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=.Range("CA1").CurrentRegion, Unique:=False
    End With
End Sub
Private Function Filter(ByVal aString As String) As String
    Filter = Trim(Replace(aString, "@@@", ""))
End Function