Macro doesnt paste value the result of the formula but the formula itself..

**extremis** · 06-27-2013, 09:54 AM

Well, i attached my file. The problem is that when I have a formula into a column, the macro doesn't do a paste/value.

Any help?

Here is the coding:

Sub CopyData()
    Application.ScreenUpdating = False
    Sheets("Result").Rows("2:" & Rows.Count).ClearContents
    Dim bottomD As Integer
    bottomD = Range("D" & Rows.Count).End(xlUp).row
    Dim x As Long
    For x = 2 To bottomD
        Cells(x, 1).Resize(, 4).Copy Sheets("Result").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
        If Cells(x, "E") = "MATCH" And Cells(x, "K") <> 0 Then
            Cells(x, "K").Copy
            Sheets("Result").Cells(Rows.Count, "H").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        ElseIf Cells(x, "E") = "MATCH" And Cells(x, "K") = 0 Then
            Cells(x, "G").Copy
            Sheets("Result").Cells(Rows.Count, "H").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        ElseIf Cells(x, "E") = "MATCH" Then
            Cells(x, "D").Copy
            Sheets("Result").Cells(Rows.Count, "H").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        ElseIf Cells(x, "E") = "NO MATCH" Then
            Cells(x, "G").Copy
            Sheets("Result").Cells(Rows.Count, "H").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        End If
        Application.CutCopyMode = False
    Next x
    Application.ScreenUpdating = True
End Subextremis.xlsm

**AB33** · 06-27-2013, 10:04 AM

Option Compare Text
Sub CopyData()
    Application.ScreenUpdating = False
     Dim bottomD As Long, x As Long
    Sheets("Result").Rows("2:" & Rows.Count).ClearContents
    
    With Sheets("Summary")
   
       bottomD = .Range("D" & .Rows.Count).End(xlUp).Row
    
       For x = 2 To bottomD
           .Cells(x, 1).Resize(, 3).Copy
           Sheets("Result").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
           If .Cells(x, "D") = "MATCH" And .Cells(x, "K") <> 0 Then
               .Cells(x, "K").Copy
               Sheets("Result").Cells(Rows.Count, "I").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
           ElseIf .Cells(x, "D") = "MATCH" And .Cells(x, "K") = 0 Then
               .Cells(x, "G").Copy
               Sheets("Result").Cells(Rows.Count, "I").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
           ElseIf .Cells(x, "D") = "NO MATCH" Then
               .Cells(x, "G").Copy
               Sheets("Result").Cells(Rows.Count, "I").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
           End If
           Application.CutCopyMode = False
       Next x
    
    End With
    Application.ScreenUpdating = True
End Sub

**extremis** · 06-27-2013, 10:09 AM

what was the problem?

**AB33** · 06-27-2013, 11:06 AM

The summary sheet has been assumed as active sheet. It is not a good idea to use an active sheet as one step out will screw up things.
For e.g

bottomD = Range("D" & Rows.Count).End(xlUp).row

could come from any sheet where ever my cursor is at any point in time. I had adjusted the code so that all input data come from summary sheet, not any other sheet.