VBA that pulls data based on multiple criteria

[Mouse7681]

am trying to create an audit template. Basically, we are trying to review settings in an application that has multiple environments. We use these environments for development, testing, training, and production. Currently, if I want to look at the settings for one role in each environment, I end up with five rows of data to try to compare. I want to be able to look at the base role and see all permissions for each environment in one row rather than five. Most of the macros run correctly however No3 is my problem. When it runs it is taking no less than 30 minutes to run. I can't figure out the best way to write the code to have it run in a timely manner and pull the data correctly. Any and all help would be greatly appreciated.

I currently have raw data in the 'SW Raw Data' sheet to play with. What I am looking for it to do is....

1. Data will be added to the 'SW Raw Data' sheet - This is done by exporting the data from our application and pasting it into the sheet. We want to keep this data raw for compliance reasons.
2. The user will then click the Analysis Setup button on the 'Instructions' sheet. This will then call each macro.
   -I broke each one out due to the length of time it was taking to run as a whole.
3. Macro No1_SW_Raw will run and pull the data from the 'SW Raw Data' sheet to the appropriate columns on the 'Security Workbench' sheet. - This works correctly.
4. Macro No2_SW_Comparison will run and pull the data from the 'Security Workbench' sheet to the appropriate columns on the 'Comparison' sheet. - This works correctly.
5. Macro No3_Pull_Env_Data needs to do a comparison of the Helper1 column and the specified environment on the 'Comparison' sheet by looking at the 'Security Workbench' sheet and if they match pull the appropriate value for the specified column to the 'Comparison' sheet.
   -Examples
   --Helper1 = ITADMIN*ALL1Action Security (this is a concatenation of multiple columns but are not unique)
   --Environment = Sheet 'Comparison' = PROD Security Type = Sheet 'Security Workbench' environment column =JP
   --if both match, pull Security Type value from the 'Security Workbench' sheet for that specific match
   --repeat this for each row and each column on 'Comparison' sheet

I have tried using VLOOKUP but it pulls the data for the first match found since the helper1 column is not unique. I switched to Index/Match thinking it would be faster. It works and pulls the data correctly; it just takes a long time.

1. Marco No4_Match_Columns will run and determine if each environment for the specified permission matches. If they do return 'Match', if not return 'Review'. We basically want the permissions for each environment to be the same and if they aren't we need to investigate why. - This works correctly.
2. No_5_Conditional_Formatting will run and apply the designated formatting for the specified columns. - This works correctly.
3. No6_Cleanup will run and do the last bit of cleanup. - This works correctly. -This will delete the helper1 column on the 'Comparison' tab. -Copy and paste the whole sheet as values (doing this so the file saves smaller and overwrites the formulas with the values).

By the end of all the macros it should look like below:

Link to the file is below. JDE Security Workbench Audit Template.xlsm

No3_Pull_Env_Data Code (small portion of the code):

Sub No3_Pull_Environment_Data()

'Turn off auto calculations
    Application.Calculation = xlCalculationManual
'Turn off screen updates
    Application.ScreenUpdating = False
'Disable Events
    Application.EnableEvents = False

' Environment Macro
' Compares the Helper1 columns and Environment columns on Security workbench and places an X in the corresponding environment.
    ActiveWorkbook.Sheets("Comparison").Activate
    Range("E2").Select
    Range("TBL_Comparison[PROD User / Role]").FormulaR1C1 = _
        "=IF(COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""*ALL"")+COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""JP""),""X"","""")"
    Range("F2").Select
    Range("TBL_Comparison[UAT User / Role]").FormulaR1C1 = _
        "=IF(COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""*ALL"")+COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""JU""),""X"","""")"
    Range("G2").Select
    Range("TBL_Comparison[TRN User / Role]").FormulaR1C1 = _
        "=IF(COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""*ALL"")+COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""JT""),""X"","""")"
    Range("H2").Select
    Range("TBL_Comparison[QA User / Role]").FormulaR1C1 = _
        "=IF(COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""*ALL"")+COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""JQ""),""X"","""")"
    Range("I2").Select
    Range("TBL_Comparison[DEV User / Role]").FormulaR1C1 = _
        "=IF(COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""*ALL"")+COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""JS""),""X"","""")"
    Range("M2").Select
    
' Security Type Macro
' Compares the Helper1 columns and Environment columns on Security workbench and pulls the security type otherwise leave blank.
    Range("M2").Select
    Range("TBL_Comparison[PROD Security Type]").Formula2R1C1 = _
        "=IF(COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""*ALL"")+COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""JP""),INDEX(Sec_Workbench,MATCH(1,(((Sec_Workbench[Environment]=""*ALL"")+(Sec_Workbench[Environment]=""JP""))>0)*(Sec_Workbench[Helper1]=[@Helper1]),0),12),"""")"
    Range("N2").Select
    Range("TBL_Comparison[UAT Security Type]").Formula2R1C1 = _
        "=IF(COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""*ALL"")+COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""JU""),INDEX(Sec_Workbench,MATCH(1,(((Sec_Workbench[Environment]=""*ALL"")+(Sec_Workbench[Environment]=""JU""))>0)*(Sec_Workbench[Helper1]=[@Helper1]),0),12),"""")"
    Range("O2").Select
    Range("TBL_Comparison[TRN Security Type]").Formula2R1C1 = _
        "=IF(COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""*ALL"")+COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""JT""),INDEX(Sec_Workbench,MATCH(1,(((Sec_Workbench[Environment]=""*ALL"")+(Sec_Workbench[Environment]=""JT""))>0)*(Sec_Workbench[Helper1]=[@Helper1]),0),12),"""")"
    Range("P2").Select
    Range("TBL_Comparison[QA Security Type]").Formula2R1C1 = _
        "=IF(COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""*ALL"")+COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""JQ""),INDEX(Sec_Workbench,MATCH(1,(((Sec_Workbench[Environment]=""*ALL"")+(Sec_Workbench[Environment]=""JQ""))>0)*(Sec_Workbench[Helper1]=[@Helper1]),0),12),"""")"
    Range("Q2").Select
    Range("TBL_Comparison[DEV Security Type]").Formula2R1C1 = _
        "=IF(COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""*ALL"")+COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""JS""),INDEX(Sec_Workbench,MATCH(1,(((Sec_Workbench[Environment]=""*ALL"")+(Sec_Workbench[Environment]=""JS""))>0)*(Sec_Workbench[Helper1]=[@Helper1]),0),12),"""")"
    Range("S2").Select
    
' Security Description Macro
' Compares the Helper1 columns and Environment columns on Security workbench and pulls the security description otherwise leave blank.
    Range("S2").Select
    Range("TBL_Comparison[PROD Description]").Formula2R1C1 = _
         "=IF(COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""*ALL"")+COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""JP""),INDEX(Sec_Workbench,MATCH(1,(((Sec_Workbench[Environment]=""*ALL"")+(Sec_Workbench[Environment]=""JP""))>0)*(Sec_Workbench[Helper1]=[@Helper1]),0),13),"""")"
    Range("T2").Select
    Range("TBL_Comparison[UAT Description]").Formula2R1C1 = _
         "=IF(COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""*ALL"")+COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""JU""),INDEX(Sec_Workbench,MATCH(1,(((Sec_Workbench[Environment]=""*ALL"")+(Sec_Workbench[Environment]=""JU""))>0)*(Sec_Workbench[Helper1]=[@Helper1]),0),13),"""")"
    Range("U2").Select
    Range("TBL_Comparison[TRN Description]").Formula2R1C1 = _
         "=IF(COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""*ALL"")+COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""JT""),INDEX(Sec_Workbench,MATCH(1,(((Sec_Workbench[Environment]=""*ALL"")+(Sec_Workbench[Environment]=""JT""))>0)*(Sec_Workbench[Helper1]=[@Helper1]),0),13),"""")"
    Range("V2").Select
    Range("TBL_Comparison[QA Description]").Formula2R1C1 = _
         "=IF(COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""*ALL"")+COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""JQ""),INDEX(Sec_Workbench,MATCH(1,(((Sec_Workbench[Environment]=""*ALL"")+(Sec_Workbench[Environment]=""JQ""))>0)*(Sec_Workbench[Helper1]=[@Helper1]),0),13),"""")"
    Range("W2").Select
    Range("TBL_Comparison[DEV Description]").Formula2R1C1 = _
         "=IF(COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""*ALL"")+COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""JS""),INDEX(Sec_Workbench,MATCH(1,(((Sec_Workbench[Environment]=""*ALL"")+(Sec_Workbench[Environment]=""JS""))>0)*(Sec_Workbench[Helper1]=[@Helper1]),0),13),"""")"
    Range("Y2").Select
    
' Sec_Fun Macro
' Compares the Helper1 columns and Environment columns on Security workbench and pulls the function setting otherwise leave blank.

'Install Function
    Range("Y2").Select
    Range("TBL_Comparison[PROD Install]").Formula2R1C1 = _
        "=IF(COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""*ALL"")+COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""JP""),INDEX(Sec_Workbench,MATCH(1,(((Sec_Workbench[Environment]=""*ALL"")+(Sec_Workbench[Environment]=""JP""))>0)*(Sec_Workbench[Helper1]=[@Helper1]),0),14),"""")"
    Range("X2").Select
    Range("TBL_Comparison[UAT Install]").Formula2R1C1 = _
        "=IF(COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""*ALL"")+COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""JU""),INDEX(Sec_Workbench,MATCH(1,(((Sec_Workbench[Environment]=""*ALL"")+(Sec_Workbench[Environment]=""JU""))>0)*(Sec_Workbench[Helper1]=[@Helper1]),0),14),"""")"
    Range("AA2").Select
    Range("TBL_Comparison[TRN Install]").Formula2R1C1 = _
        "=IF(COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""*ALL"")+COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""JT""),INDEX(Sec_Workbench,MATCH(1,(((Sec_Workbench[Environment]=""*ALL"")+(Sec_Workbench[Environment]=""JT""))>0)*(Sec_Workbench[Helper1]=[@Helper1]),0),14),"""")"
    Range("AB2").Select
    Range("TBL_Comparison[QA Install]").Formula2R1C1 = _
        "=IF(COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""*ALL"")+COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""JQ""),INDEX(Sec_Workbench,MATCH(1,(((Sec_Workbench[Environment]=""*ALL"")+(Sec_Workbench[Environment]=""JQ""))>0)*(Sec_Workbench[Helper1]=[@Helper1]),0),14),"""")"
    Range("AC2").Select
    Range("TBL_Comparison[DEV Install]").Formula2R1C1 = _
        "=IF(COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""*ALL"")+COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""JS""),INDEX(Sec_Workbench,MATCH(1,(((Sec_Workbench[Environment]=""*ALL"")+(Sec_Workbench[Environment]=""JS""))>0)*(Sec_Workbench[Helper1]=[@Helper1]),0),14),"""")"
    Range("AE2").Select
    
'Run Function
    Range("AE2").Select
    Range("TBL_Comparison[PROD Run]").Formula2R1C1 = _
        "=IF(COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""*ALL"")+COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""JP""),INDEX(Sec_Workbench,MATCH(1,(((Sec_Workbench[Environment]=""*ALL"")+(Sec_Workbench[Environment]=""JP""))>0)*(Sec_Workbench[Helper1]=[@Helper1]),0),15),"""")"
    Range("AF2").Select
    Range("TBL_Comparison[UAT Run]").Formula2R1C1 = _
        "=IF(COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""*ALL"")+COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""JU""),INDEX(Sec_Workbench,MATCH(1,(((Sec_Workbench[Environment]=""*ALL"")+(Sec_Workbench[Environment]=""JU""))>0)*(Sec_Workbench[Helper1]=[@Helper1]),0),15),"""")"
    Range("AG2").Select
    Range("TBL_Comparison[TRN Run]").Formula2R1C1 = _
        "=IF(COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""*ALL"")+COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""JT""),INDEX(Sec_Workbench,MATCH(1,(((Sec_Workbench[Environment]=""*ALL"")+(Sec_Workbench[Environment]=""JT""))>0)*(Sec_Workbench[Helper1]=[@Helper1]),0),15),"""")"
    Range("AH2").Select
    Range("TBL_Comparison[QA Run]").Formula2R1C1 = _
        "=IF(COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""*ALL"")+COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""JQ""),INDEX(Sec_Workbench,MATCH(1,(((Sec_Workbench[Environment]=""*ALL"")+(Sec_Workbench[Environment]=""JQ""))>0)*(Sec_Workbench[Helper1]=[@Helper1]),0),15),"""")"
    Range("AI2").Select
    Range("TBL_Comparison[DEV Run]").Formula2R1C1 = _
        "=IF(COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""*ALL"")+COUNTIFS(Sec_Workbench[Helper1],[@Helper1],Sec_Workbench[Environment],""JS""),INDEX(Sec_Workbench,MATCH(1,(((Sec_Workbench[Environment]=""*ALL"")+(Sec_Workbench[Environment]=""JS""))>0)*(Sec_Workbench[Helper1]=[@Helper1]),0),15),"""")"
    Range("AK2").Select
End Sub