I'm running a macro in Excel VBA and one of the modules I have keeps restarting the macro when it tries to execute the Range("K2:K" & LR).Calculate line of code. I've used the .calculate function in macros before and never had this happen. I'm unsure why this keeps happening in this case. Could someone take a look at my code and see if you can spot a reason for this strange restart behavior? Calculations have already been set to manual using Application.Calculation = xlCalculationManual in a prior module within the same project.
Private Sub L2Leadership_Selection_Change()
Dim L2Name As String
Dim Vertical_Selection As String
Dim L2Leadership_Selection As String
Dim Dept As String
Vertical_Selection = Hiring_Validation_Form.Vertical_Selection.Value
L2Leadership_Selection = Hiring_Validation_Form.L2Leadership_Selection.Value
Workbooks(ToolName).Activate
Sheets(2).Select
Range("I2:K50").Select
Selection.Clear
Sheets("Sheet1").Activate
Columns("O:P").Clear
Range("N41:N100").Clear
Range("K2:L3").Clear
Range("K2").Select
ActiveCell.Value = L2Leadership_Selection
Selection.TextToColumns Destination:=Range("K2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("K3").Select
ActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
Range("L3").Select
ActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
L2Name = Cells(3, 11).Value
Dept = Cells(3, 12).Value
'Filter CC Mapping by Vertical & L2
Sheets("CC Mapping").Select
Range("D1").Select
On Error Resume Next
ActiveSheet.ShowAllData
ActiveSheet.Range("$A$1:$J$25683").AutoFilter Field:=4, Criteria1:=Vertical_Selection
ActiveSheet.Range("$A$1:$J$25683").AutoFilter Field:=5, Criteria1:=L2Name
ActiveSheet.Range("$A$1:$J$25683").AutoFilter Field:=7, Criteria1:="<>Inactive", Criteria2:="<>Non-CS"
On Error GoTo 0
'Copies Dept Name & CC results
ActiveSheet.Range("$A$2:$B$25683").SpecialCells(xlCellTypeVisible).Copy
Sheets(2).Select
Range("I2").Select
Selection.PasteSpecial Paste:=xlPasteValues
'Sets Definition for Department Name
LR = Cells(Rows.Count, 9).End(xlUp).Row
Range("K2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""CC ""&RC[-2]&"" - ""&RC[-1])"
Range("K2").Select
On Error Resume Next
Selection.AutoFill Destination:=Range("K2:K" & LR), Type:=xlFillDefault
Range("K2:K" & LR).Calculate 'This is the line that when stepping through line by line, it jumps back up to the Private Sub L2Leadership_Selection_Change() line and restarts the macro when this line is executed.
On Error GoTo 0
'ActiveSheet.Range("K2:K" & LR).Calculate
ActiveWorkbook.Names("DeptName").RefersToR1C1 = "=Data!R2C11:R20C11"
End Sub
I've also tried just setting this specific module to automatic calculation using Application.Calculation = xlCalculationAutomatic but then for some weird reason it often doesn't switch between worksheets that I specify in the code. For instance when I want it to reference Sheets("Sheet1").select, it doesn't switch from the worksheet it is currently one when it starts the macro, to sheets ("Sheet1") and so none of the code that is supposed to happen on that sheet works.
