VBA: Copy and paste defined range as Values (123)

[Lebene]

Hello everyone,

I need an easier way to copy cells within a certain range and then paste in the same range as values. It's just that once the cells are filled with info from the index/match, I want to paste it as values so that the values don't change (it's people records and they might move to another department or get a promotion so the details could change once I update the main people sheet).

How could I do this? I would need a navigation box to open that lets me input the range and when I click 'go' or 'run' it does that for me. When you're on <500 records it's easier to do it manually but as the records grow I keep forgetting where I was and having to 'show formula' and it gets arduous and opens up to more human error.

Thanks for any advice or help

 

[Anthony47]

Try this macro:

Sub FixValue()
Dim myR As Range
'
On Error Resume Next
Set myR = Application.InputBox("Select Range to fix values", "select...", Selection.Address, , , , , 8)
On Error GoTo 0
If Not myR Is Nothing Then
    myR.Value = myR.Value
    myR.Cells(1, 1).Select
Else
    Beep
End If
End Sub

It will ask to select a range (default value is the current selection) and then set it with current values

Bye

 

[Lebene]

Try this macro:

Sub FixValue()
Dim myR As Range
'
On Error Resume Next
Set myR = Application.InputBox("Select Range to fix values", "select...", Selection.Address, , , , , 8)
On Error GoTo 0
If Not myR Is Nothing Then
    myR.Value = myR.Value
    myR.Cells(1, 1).Select
Else
    Beep
End If
End Sub

It will ask to select a range (default value is the current selection) and then set it with current values

Bye

You're a lifesaver, thank you very much.

Bye

 

[Anthony47]