While Excel will set this back to True whenever focus is passed back to Excel (your macro finishes) in most cases, it pays to play it safe and include the code at the end.
A simple way is to use Object variables with a descriptive name: Dim o Timesheet as Worksheet Set o Timesheet=Thisworkbook.
Activate lines when switching between two worksheets. The procedure: Sub Get Share Values() ' ' Reads share prices from FTSE100 sheet for my Portfolio Dim rng Share As Range Dim rng Share Find As Range Dim var Share Value(1 To 3) As Variant Dim str Share Name(1 To 3) As String Dim x As Integer Dim rng As Range Application. Activate 'Goto FTSE sheet 'Look for each share name on the FTSE sheet then get the current quote For x = 1 To 3 Cells.
Whenever I set one or both as a range object, the 'Find' line throws up errors, usually that the Find Method is not supported by the object or the object isn't valid. Screen Updating = False Set rng Share = This Workbook. Range("A3") 'Get names of shares into array For x = 1 To 3 str Share Name(x) = rng Share. Find(What:=str Share Name(x), After:=Active Cell, Look In:=xl Formulas, _ Look At:=xl Part, Search Order:=xl By Rows, Search Direction:=xl Next, _ Match Case:=False, Search Format:=False).
By setting Screen Updating to False at the Start of the macro, you will not only stop the constant screen flickering associated with recorded macro, but also greatly speed up the execution of the macro.
The reason it speeds up code is because Excel no longer needs to repaint the screen whenever it encounters such commands as Select, Activate, Large Scroll, Small Scroll and many others. Screen Updating=False should be placed at the Start of your macro like shown below Note how we have set the Screen Updating back to True on completion.