vba – How can I speed up this 2d array script?

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?