This thread is related to this thread:https://www.excelforum.com/excel-pro...ula-parts.html
I can't post the workbook, because it's a huge beast with many interconnected parts.
What I want to do is change "2017/2017 05 May" to "2017/2017 06 Jun" (the values will change from month to month, but this is the basic operation).
There are approximately 33K Vlookup formulas in the workbook that would be affected by the change, and they exist on only 2 tabs. However, there are over 100K vlookups total in this workbook, and many tabs, and I think my macro is causing all of them to recalculate. Running the code below takes a full hour to complete, and there's got to be a better way.
I've noticed that if I comment out the "SpeedOff" macro the process completes quickly (because no calculation is happening, so no formulas are refreshing). So, I'm thinking if I could limit the calculation to only those two tabs it would be a quicker process, but not sure how to do that, and not entirely sure it would do the trick. I'd sure appreciate any help I could get on this.
Here are the two links I'm wanting to change (these are the ones the macro is using this time):
New Path \\12aust1001fs01\share10011\Budget\SOBUDGET\Regular Monthly\Projections\CY 2017\2017 06 Jun Projection\
Old Path \\12aust1001fs01\share10011\Budget\SOBUDGET\Regular Monthly\Projections\CY 2017\2017 05 May Projection\
Sub Updateformulas()
SpeedOn
Dim arrLinks
Dim i As Long
Dim Old As String
Dim Nuu As String
Nuu = ThisWorkbook.Sheets("Path").Range("B4").Value
Old = ThisWorkbook.Sheets("Path").Range("B5").Value
arrLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(arrLinks) Then
On Error Resume Next
Application.DisplayAlerts = False
For i = LBound(arrLinks) To UBound(arrLinks)
ActiveWorkbook.ChangeLink arrLinks(i), Replace$(arrLinks(i), Old, Nuu), xlLinkTypeExcelLinks
Next i
Application.DisplayAlerts = True
End If
SpeedOff
MsgBox "All Done"
End Sub
Sub SpeedOn() '(Optional StatusBarMsg As String = "Running macro...")
'glb_origCalculationMode = Application.Calculation
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.Cursor = xlWait
'.StatusBar = StatusBarMsg
.EnableCancelKey = xlErrorHandler
End With
End Sub
Sub SpeedOff()
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
.CalculateBeforeSave = True
.Cursor = xlDefault
.StatusBar = False
.EnableCancelKey = xlInterrupt
End With
End Sub
Bookmarks