Is VBA function possible without using the clipboard; make all sheets values only

Question

I have the following function using Excel 2010:

Private Function MakeAllSheetsValuesOnly(targetBookName As String)

If Excel.ActiveWorkbook.Name = Excel.ThisWorkbook.Name Then
Else

        Excel.Workbooks(targetBookName).Activate

        Dim mySheet
        For Each mySheet In Excel.ActiveWorkbook.Sheets
                    With mySheet
                                With .Cells
                                            .Copy
                                            .PasteSpecial Excel.xlPasteValues
                                End With
                                .Select
                                .Range("A1").Select
                    End With
                    Excel.ActiveWindow.SmallScroll Down:=-200
                    Excel.Application.CutCopyMode = False
        Next mySheet
End If

End Function                        'MakeAllSheetsValuesOnly

It works but I'd rather not rely on the clipboard is there an alternative way to make all sheets values only?

Just found an alternative logic I've been using in another program which is relevent to this topic:

Dim rSource As Range
Dim rDest As Range

Set rSource = .Range("C5:BG" & .Range("B4").Value + 4)   
Set rDest = mySummaryBook.Sheets("Data_Measures").Cells(Rows.Count, 4).End(xlUp)(2, 1)
    With rSource
          Set rDest = rDest.Resize(.Rows.Count, .Columns.Count)
    End With
    rDest.Value = rSource.Value
Set rSource = Nothing
Set rDest = Nothing

Answer 1

Maybe something like this:

With mySheet.UsedRange
    .Value = .Value
End With

Answer 2

You don't need a function for this.

Tim has already given you a great method. Here is another way...

Sub Sample()
    MakeAllSheetsValuesOnly "Book2"
End Sub

Private Sub MakeAllSheetsValuesOnly(targetBookName As String)
    Dim mySheet As Worksheet
    Dim formulaCell As Range
    Dim aCell As Range

    Application.ScreenUpdating = False

    For Each mySheet In Workbooks(targetBookName).Sheets
        On Error Resume Next
        Set formulaCell = mySheet.Cells.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0

        If Not formulaCell Is Nothing Then
            For Each aCell In formulaCell
                aCell.Value = aCell.Value
            Next
        End If
    Next mySheet

    Application.ScreenUpdating = True
End Sub