CODEWITHSUNDEEP
excelvba
Shaun Casey
Shaun Casey

Reputation: 69

Copy cell range from one worksheet and paste in a different worksheet as a value rather than formula

I have this code below to copy a range of cells and paste to the next available row on a different worksheet. My issue is that it's pasting the formulas rather than the values. What do I need to change?

Private Sub CopyAuditData_Click()
Dim ws1 As Worksheet, ws2 As Worksheet
    Dim DestRow As Long
    Set ws1 = Sheets("Call Audit Sheet")
    Set ws2 = Sheets("HiddenData")
    DestRow = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1
    ws1.Range("V1").Copy ws2.Range("A" & DestRow)
    ws1.Range("V2").Copy ws2.Range("B" & DestRow)
    ws1.Range("V3").Copy ws2.Range("C" & DestRow)
    ws1.Range("V4").Copy ws2.Range("D" & DestRow)
    ws1.Range("V5").Copy ws2.Range("E" & DestRow)
    ws1.Range("V6").Copy ws2.Range("F" & DestRow)
    ws1.Range("V7").Copy ws2.Range("G" & DestRow)
    ws1.Range("V8").Copy ws2.Range("H" & DestRow)
    ws1.Range("V9").Copy ws2.Range("I" & DestRow)
    ws1.Range("V10").Copy ws2.Range("J" & DestRow)
    ws1.Range("V11").Copy ws2.Range("K" & DestRow)
    ws1.Range("V12").Copy ws2.Range("L" & DestRow)
    ws1.Range("V13").Copy ws2.Range("M" & DestRow)
    ws1.Range("V14").Copy ws2.Range("N" & DestRow)
    ws1.Range("V15").Copy ws2.Range("O" & DestRow)
    ws1.Range("V16").Copy ws2.Range("P" & DestRow)
    ws1.Range("V17").Copy ws2.Range("Q" & DestRow)
    ws1.Range("V18").Copy ws2.Range("R" & DestRow)
    ws1.Range("V19").Copy ws2.Range("S" & DestRow)
    ws1.Range("V20").Copy ws2.Range("T" & DestRow)
    ws1.Range("V21").Copy ws2.Range("U" & DestRow)
    ws1.Range("V22").Copy ws2.Range("V" & DestRow)
    ws1.Range("V23").Copy ws2.Range("W" & DestRow)
    ws1.Range("V24").Copy ws2.Range("X" & DestRow)
    ws1.Range("V25").Copy ws2.Range("Y" & DestRow)
End Sub

Thanks in advance guys.

Upvotes: 0

Views: 1967

Answers (3)

peege
peege

Reputation: 2477

This is essentially transposing rows to columns from one worksheet to another.

Instead of the many lines of copy/paste with specific ranges, use a loop, Reducing 25-50 lines into 3.

To be used in your specific example, because you happen to be transposing rows to columns. The row number happens to match the column number you are pasting into, so embrace it with a loop. 

Private Sub CopyAuditData_Click()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim DestRow As Long, lRow As Long

    Set ws1 = Sheets("Call Audit Sheet")
    Set ws2 = Sheets("HiddenData")
    DestRow = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1

    'CHANGES HERE
    For lRow = 1 To 25
        ws2.Cells(DestRow, lRow).Value = ws1.Cells(lRow, "V").Value 
    Next lRow

End Sub

Breaks down like:

Where lRow = 1 Using it as both the row of the source and column of target.

ws2.Cells(DestRow, 1) is the same as ws2.Range("A" & DestRow)

ws1.Cells(lRow, "V") is the same as ws1.Range("V1")

Upvotes: 1

Kᴀτᴢ
Kᴀτᴢ

Reputation: 2176

Try this for copy only the values:

Instead of

ws1.Range("V1").Copy ws2.Range("A" & DestRow)

use

ws2.Range("A" & DestRow) = ws1.Range("V1")

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57683

Instead of 

ws1.Range("V1").Copy ws2.Range("A" & DestRow)

do

ws1.Range("V1").Copy
ws2.Range("A" & DestRow).PasteSpecial Paste:=xlPasteValues

Note: Has to be 2 lines of code now!

Upvotes: 0

Related Questions