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

excel, vba

Solution

Maybe something like this:

With mySheet.UsedRange
    .Value = .Value
End With

Problem

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 ```

Original source