Copy (only value and not formula) range of cells to another sheet in same file

I am using the following code. This code copies the cell formulas into the other sheet. Does anyone know how can I modify it to just copy the cell values and not formulas?


sub copyRangeOver()

    Dim i As Integer
    i = 631

    Dim copyRange  As Range
    Set copyRange = ThisWorkbook.Worksheets("Coupling xyz data").Range("J" & 1 & ":V" & i)

    Dim countD As Integer
    countD = 1
    copyRange.Copy Destination:=Cells(countD, 1)

    Dim j As Integer
    j = 466
    Set copyRange = ThisWorkbook.Worksheets("Spring xyz data").Range("J" & 1 & ":V" & j)

    copyRange.Copy Destination:=Cells(i + 1, 1)

End Sub

Answers


You can do a direct value transfer which is faster than a Copy and Paste Special, Values and doesn't involve the clipboard.

Sub copyRangeOver()

    Dim i As Long, j As Long, countD As Long
    Dim copyRange As Range

    i = 631
    countD = 1

    With ThisWorkbook.Worksheets("Coupling xyz data")
        Set copyRange = .Range("J" & 1 & ":V" & i)
        Cells(countD, 1).Resize(copyRange.Rows.Count, copyRange.Columns.Count) = _
            copyRange.Value
    End With

    j = 466

    With ThisWorkbook.Worksheets("Spring xyz data")
        Set copyRange = .Range("J" & 1 & ":V" & j)
        Cells(i + 1, 1).Resize(copyRange.Rows.Count, copyRange.Columns.Count) = _
            copyRange.Value
    End With

End Sub

That duplicates your previous effort although it seems to me that there is some ambiguity in the destination(s) of the values. For a row to receive the values, you are using i in the second one and CountD in the first but there doesn't appear to be a relationship between them.


Need Your Help

Delimiters inside a textbox

javascript asp.net html

I have a spec that is saying that a certain field needs to be in the format "XX/XXXX/XXXXX". How do I add in read-only /'s inside of a textbox?

Lightweight cross platform C GUI (OpenGL)

android c user-interface ios opengl-es

I am looking for a lightweight cross platform GUI library for different signal-processing applications. An ANSI-C program must be statically linked with it and the size matters. A finished applicat...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.