HELP >.< Ammend VBA code to paste special values

[Phisig500]

I've been working for countless hours on this. I am trying to convert the following code to paste special values instead of just past values. The source data is powerpivot (DAX) formulas, so i get error messages in the pasted data once the macro runs.

My code is:

SubSub TestagainSAFE()
Dim i As Long
Dim lr1 As Long, lr2 As Long
Dim Delta As String
Dim wks1 As Worksheet, wks2 As Worksheet
Set wks1 = Worksheets("Sheet1")
Set wks2 = Worksheets("Sheet2")
lr1 = wks1.Cells(Rows.Count, "Y").End(xlUp).Row
For i = 2 To lr1
Delta = wks1.Cells(i, "Y").Value
If Not IsEmpty(Len(Delta)) Then
If Len(Delta) <> 0 Then
lr2 = wks2.Cells(Rows.Count, "A").End(xlUp).Row + 1
wks1.Cells(i, "Y").EntireRow.Copy Destination:=wks2.Cells(lr2, "A")
End If
End If
Next i
MsgBox "SPI financial inquiries have been submitted", vbInformation
End Sub



Thank you for all your help!!!

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

You will need to use PasteSpecial and that takes two lines:

Sub TestagainSAFE()
    Dim i As Long
    Dim lr1 As Long, lr2 As Long
    Dim Delta As String
    Dim wks1 As Worksheet, wks2 As Worksheet
    Set wks1 = Worksheets("Sheet1")
    Set wks2 = Worksheets("Sheet2")
    lr1 = wks1.Cells(Rows.Count, "Y").End(xlUp).Row
    For i = 2 To lr1
        Delta = wks1.Cells(i, "Y").Value
        If Not IsEmpty(Len(Delta)) Then
            If Len(Delta) <> 0 Then
                lr2 = wks2.Cells(Rows.Count, "A").End(xlUp).Row + 1
                wks1.Cells(i, "Y").EntireRow.Copy
                wks2.Cells(lr2, "A").PasteSpecial (xlPasteValues)
            End If
        End If
    Next i
    MsgBox "SPI financial inquiries have been submitted", vbInformation
End Sub

 

[Phisig500]

Thanks a million RickXL. After a few hours of trial an error, I actually figured it out, but am happy I can double check against yours. Thanks again!!

 

[MARK858]

Could you not do...?

If Len(Delta) <> 0 Then
                lr2 = wks2.Cells(Rows.Count, "A").End(xlUp).Row + 1
            [COLOR="#FF0000"]wks2.Cells(lr2, "A").EntireRow.Value = wks1.Cells(i, "Y").EntireRow.Value[/COLOR]
            End If

although normally I would use a last column and then resize the destination range rather than use entirerow