Open de File with a sheet filtered according to a matriz

TEIXEIRE

New Member
Joined
Apr 17, 2020
Messages
27
Office Version
  1. 365
Platform
  1. Windows
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")
UserMain ResponsibleCountrySBU
Eloize TeixeiraLogisticsBrazil, Chile
Leonardo ContinCustomer ServiceBrazil
Mariana MachadoSupply ChainBrazilACC
Application.UserNameData(AO:AO)Data(AK:AK)Data(AL:AL)
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Is it possible for Country to have MORE THAN 2 options ?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Advanced Filter can handle everything you require
I will update the thread with code for you to test within the next 24 hours
 

TEIXEIRE

New Member
Joined
Apr 17, 2020
Messages
27
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

thanks so much ! :love: :love:
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
In the meantime look at these links and make sure you understand how advanced filter works

 

TEIXEIRE

New Member
Joined
Apr 17, 2020
Messages
27
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,576
Messages
5,637,175
Members
416,960
Latest member
Carbon1198

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top