Paste Special(Values) into areas (Excel 2000)

[ChrisGreaves]

Here's another one. The workbooks have multiple sheets, each sheet drawing data from other sheets. I identify a suitable candidate as a master sheet and want to fix values brought in from other sheets and use those value cells as a user input area.
The macro "FixValues" allows me to Click-Ctrl-Click-Ctrl-Click to build a selection, then fixes the values all at once.

Code: Select all

Sub FixValues()
    Call FixValuesRange(Selection)
End Sub
Sub FixValuesAllWorksheets()
    Dim wks As Worksheet
    For Each wks In ActiveWorkbook.Worksheets
        Call FixValuesRange(wks.UsedRange)
    Next wks
End Sub
Function FixValuesRange(rng As Range)
    Dim rngAr As Range
    For Each rngAr In rng.Areas
        rngAr.Copy
        rngAr.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Next rngAr
End Function

[HansV]

Another tiny comment: the two lines

Code: Select all

        rngAr.Copy
        rngAr.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

can be replaced with

Code: Select all

        rngAr.Value = rngAr.Value

[ChrisGreaves]

Another tiny comment:

Thanks hans.
That's what I get for recording a macro ....