Spikenaylor
Board Regular
- Joined
- Apr 14, 2013
- Messages
- 116
- Office Version
- 365
- Platform
- Windows
This used to be quite fast, nothing has changed except software updates and office updates. now using business office 365
standard stuff set including
Application.Calculation = xlManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
have tried this as a test
This coding only gets run once a year when updating the spreadsheet for a new year.
both work but extremely slowly, like I said the first code worked fine upto some point. No Idea why code like this runs very slowly now?
anyone got any ideas or guidance
thanks for looking
Regards
standard stuff set including
Application.Calculation = xlManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
VBA Code:
For i = 2 To 1801
Application.StatusBar = "Updating Pivot Sheet data links for row " & i
Range("EY" & i & ":GI" & i).Replace strCurrentYear, strNewYear, xlPart
Next i
have tried this as a test
VBA Code:
For i = 2 To 1801
Application.StatusBar = "Updating IPC Pivot Sheet data links for row " & i
Set acell = ActiveSheet.Range("EY" & i & ":GI" & i).Find(What:=strCurrentYear, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not acell Is Nothing Then
acell.Formula = Replace(acell.Formula, strCurrentYear, strNewYear)
End If
Next i
This coding only gets run once a year when updating the spreadsheet for a new year.
both work but extremely slowly, like I said the first code worked fine upto some point. No Idea why code like this runs very slowly now?
anyone got any ideas or guidance
thanks for looking
Regards