Solved: Instructions Following a Called Script

[Opv]

'CONVERT FORMULAS TO VALUES
Sub convFormulasToValues()

Dim lastRecRow As Integer
Dim formulaRng As Range

With Sheets("Checking")

On Error GoTo closeIt

lastRecRow = .Range("Rec").End(xlDown).Row
Set formulaRng = .Range("K4:K" & lastRecRow).SpecialCells(xlCellTypeFormulas)

If Right(lastRecRow, 2) = "00" And formulaRng.Count > 0 Then
Application.ScreenUpdating = False
formulaRng.Copy
formulaRng.PasteSpecial (xlPasteValues)
Application.ScreenUpdating = True
Application.CutCopyMode = False
Else:
GoTo closeIt
End If

End With

closeIt:

End Sub


This snipped is called from another script and is doing what it is suppose to do. It is called BEFORE a simple instruction to "Select" a designated cell at the bottom of the worksheet. It works fine so long as the prescribed conditions are not met. However, if the conditions are met, something in the above script is causing the formulaRng to remain selected so that the subsequent Select instruction is apparently being ignored. Does anything jump out as being the possible culprit?

[Kenneth Hobs]

I avoid Select when I can.

If you want to select some other range after a paste when that sheet is not the active sheet, you need to activate it and select another cell as the selection for that sheet.

e.g.

Sub Test()
Dim r As Range, rr As Range
Set r = Worksheets(1).Range("A11:A12")

r.Worksheet.Activate
r.Select

With Worksheets(2)
.Unprotect "ken"
.Range("A2:A10").Locked = False
.Protect "ken"
End With

With Worksheets(2).Range("A2:A10")
.Worksheet.Activate
.Formula = "=Row()"
.Copy
.PasteSpecial xlPasteValues
Cells(.Row, .Column).Select 'Select only the first cell of range.
End With

r.Worksheet.Activate
Application.CutCopyMode = False
Cells(r.Row, r.Column).Select 'Select only the first cell of range r.
End Sub

[Opv]

I thought I had avoided Select in the script posted. The Select statement referenced in my explanation is in another script, not another worksheet, so the worksheet in question is already active. That Select statement is presumed to be needed, as it tells Excel where to scroll to and leave the cursor when I click to save the worksheet.

[Opv]

Well, I somehow corrected my own problem. Thanks anyway.

[mikerickson]

This is another way to change formulas to their values, that leaves the Clipboard un changed.


With theRange
.Value = .Value
End With

[Opv]

This is another way to change formulas to their values, that leaves the Clipboard un changed.


With theRange
.Value = .Value
End With

Very cool. Thanks.