Copy columns and paste values (not formulas)

Question

AlexP provided the following code to a question about copying columns. It works great for me except that in ws1, the columns have equations that get copied over to ws2. I just want to copy over the values, not the equations.

Sub CopyHeaders()
    Dim header As Range, headers As Range
    Set headers = Worksheets("ws1").Range("A1:Z1")

    For Each header In headers
        If GetHeaderColumn(header.Value) > 0 Then
            Range(header.Offset(1, 0), header.End(xlDown)).Copy Destination:=Worksheets("ws2").Cells(2, GetHeaderColumn(header.Value))
        End If
    Next
End Sub

Function GetHeaderColumn(header As String) As Integer
    Dim headers As Range
    Set headers = Worksheets("ws2").Range("A1:Z1")
    GetHeaderColumn = IIf(IsNumeric(Application.Match(header, headers, 0)), Application.Match(header, headers, 0), 0)
End Function

Check this out: http://www.mrexcel.com/forum/excel-questions/49124-visual-basic-applications-copy-paste-cell-value-only-not-formula.html — J.S. Orris, Feb 12 '15 at 23:23

score 0 · Accepted Answer · answered Feb 12 '15 at 23:28

Just split this line:

Range(header.Offset(1, 0), header.End(xlDown)).Copy Destination:=Worksheets("ws2").Cells(2, GetHeaderColumn(header.Value))

and use the .PasteSpecial() method instead

e.g.

Range(header.Offset(1, 0), header.End(xlDown)).Copy
Worksheets("ws2").Cells(2, GetHeaderColumn(header.Value)).PasteSpecial(xlPasteValues)
Application.CutCopyMode = False

Copy columns and paste values (not formulas)

1 Answers1