I have a script that contains manager name
Leaves ("Mgrs") and extract the employees who fall under each manager in
Worksheets ("Merge Retail Sharepoint Files"), Once it finds the manager's staff, it prints the array into a new worksheet, formats it, and saves it based on the values I set.
Option explicitly Sub Retail_Cuts () Dim j As Long, k As Long, x As Long Dim varArray () as a variant Dim varArray2 () as a variant ReDim varArray (1 to 19, 1 to 1) Dim strManager As String, strEC As String, strLogin As String Dim BASEPATH As String, strNewPath As String, strFileName As String Dim Wb as a workbook Dim mgrRow for so long Dim colManager So long colManager = 3 Dim ColLogin As Long colLogin = 4 Dim ColEC As Long colEC = 5 BASEPATH = "M: 11-19-2018 " Call Ludicrous (True) For mgrRow = 2 To ThisWorkbook.Worksheets ("Mgrs"). UsedRange.Rows.Count If ThisWorkbook.Worksheets ("Mgrs"). Cells (mgrRow, 1) <> "" Then strManager = ThisWorkbook.Worksheets ("Mgrs"). Cells (mgrRow, 1) With ThisWorkbook.Worksheets ("Merging Share Sharepoint Files") ReDim varArray (1 to UBound (varArray, 1), 1 to 1) x = 1 For k = 1 to UBound (varArray, 1) varArray (k, x) = .Cells (1, k) Next For j = 2 to .UsedRange.Rows.Count + 1 If strManager = .Cells (j, colManager), then x = x + 1 ReDim Preserve varArray (1 to UBound (varArray, 1), 1 to x) For k = 1 to UBound (varArray, 1) If k = 1, then varArray (1, x) = CStr (format (cells (j, k), "000000000")) Otherwise varArray (k, x) = .Cells (j, k) When strEC = .Cells (j, colEC) strManager = .Cells (j, colManager) strLogin = .Cells (j, colLogin) Next When Next End with strNewPath = BASEPATH & strEC & "" If Len (Dir (strNewPath, vbDirectory)) = 0, then MkDir strNewPath When strFileName = strLogin & "-" & strManager & "-" & "Move differential validation" & ".xlsx" ReDim varArray2 (1 to UBound (varArray, 2), 1 to UBound (varArray, 1)) Set Wb = Workbooks.Add (XlWBATemplate.xlWBATWorksheet) With Wb With .Worksheets ("Sheet1") Columns (1) .NumberFormat = "@" Columns (15) .NumberFormat = "0%" Columns (18) .NumberFormat = "0%" For j = 1 to UBound (varArray, 2) For k = 1 to UBound (varArray, 1) varArray2 (j, k) = varArray (k, j) Next Next .Range (.Cells (1, 1), .Cells (UBound (VarArray, 2), UBound (VarArray, 1))) = VarArray2 Columns ("N: O"). HorizontalAlignment = xlCenter Columns ("Q: Q"). HorizontalAlignment = xlLeft Columns ("R: R"). HorizontalAlignment = xlCenter Columns ("S: S"). HorizontalAlignment = xlLeft Call DataValidation Call header Call Macro1 .Range ("C2"). Choose ActiveWindow.FreezePanes = True .Cells.EntireColumn.AutoFit .Rows ("1: 1"). Font.Bold = True Protect Reputation End with .SaveAs strNewPath & strFileName, Password: = "ShiftDiff", FileFormat: = 51 .Saved = True .Shut down End with Set Wb = nothing When Next Call Ludicrous (False) End Sub
The only problem is that it takes about 1-1.2 seconds to create, store, protect, format, create two data validation lists, etc … That's it
Calling modules It's not much code, so most of the improvements would be done in the code above.
I've disabled all calculations, screen refreshes, and so on
Mischievous mode moniker
Is there a way to speed up saving / password protection? I know if you have one
CopyAs Save the feature to a template that speeds up a ton, but you can not protect a password. Any thoughts?