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....
![enter image description here enter image description here](/board/proxy.php?image=https%3A%2F%2Fi.sstatic.net%2FAJsKbCD8.png&hash=b4979f33c30bc58abeb8752dc9d0b9d2)
Link to the file is below. JDE Security Workbench Audit Template.xlsm
No3_Pull_Env_Data Code (small portion of the code):
I currently have raw data in the 'SW Raw Data' sheet to play with. What I am looking for it to do is....
- 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.
- 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. - 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.
- 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.
- 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
- 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.
- No_5_Conditional_Formatting will run and apply the designated formatting for the specified columns. - This works correctly.
- 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).
![enter image description here enter image description here](/board/proxy.php?image=https%3A%2F%2Fi.sstatic.net%2FAJsKbCD8.png&hash=b4979f33c30bc58abeb8752dc9d0b9d2)
Link to the file is below. JDE Security Workbench Audit Template.xlsm
No3_Pull_Env_Data Code (small portion of the code):
VBA 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