I currently have a Macro which searches a range for blank cells, selects all non-Blank cells within that range and Copies them. I have another Macro which Pastes them. Several of the cells that I copy include =Randbetween(0,99), and as soon as my Paste macro performs its Paste operation, all of my Randbetweens re-roll. The Randbetweens come from a different sheet, so I'd like to prevent all cells/processes from updating on the entire document, not just one sheet.
How can I prevent this macro from triggering an update/recalculation?
Sub Outcome_Copy()
Dim LR As Long, cell As Range, rng As Range
With Sheets("OUTCOME")
LR = .Range("A" & Rows.Count).End(xlUp).Row
For Each cell In .Range("A1:B67" & LR)
If cell.Value <> "" Then
If rng Is Nothing Then
Set rng = cell
Else
Set rng = Union(rng, cell)
End If
End If
Next cell
rng.Copy
End With
Application.Run "PasteValues"
End Sub
And for reference, the PasteValues is
Sub PasteValues()
Range("A101").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.LargeScroll Up:=93
End Sub
Bookmarks