Pasting with VBA

[Rogozhin]

Hi,

I'm stumped on a couple of issues and would be grateful if anyone can offer a tip on either (or both!).

I have some code which checks the values in column M of a sheet ("Timesheets Update") and if less than or equal to a specified value copies it across to another sheet ("Renewals").

The problem is that it copies across the cell formula rather than actual cell value. I think I need to use pastespecial but can't get it to work properly.

The second issue I have is that when it copies across it retains any blank rows (e.g. if only rows 5, 10 and 15 have values matching the specified criteria I end up with multiple blank rows between my results). Is there a way of avoiding this?

Private Sub CommandButton2_Click()
Dim i As Long, J As Long, d As Long
Dim k As String
d = 2
    For i = 4 To Range("M" & Rows.Count).End(xlUp).Row
        If Cells(i, 13).Value <= Sheets("Settings").Range("B2") Then
            For J = 1 To 5
            k = Choose(J, "A", "B", "F", "K", "M")
            Cells(i, k).Copy Destination:=Sheets("Renewals").Range(Chr(J + 64) & d)
            Next J
        End If
    d = d + 1
    Next i
MsgBox "Report complete"
End Sub

Thanks for any help.

[StephenR]

See if this works.

Private Sub CommandButton2_Click()

Dim i As Long, J As Long, d As Long
Dim k As String

For i = 4 To Range("M" & Rows.Count).End(xlUp).Row
    If Cells(i, 13).Value <= Sheets("Settings").Range("B2") Then
        For J = 1 To 5
            k = Choose(J, "A", "B", "F", "K", "M")
            Sheets("Renewals").Range(Chr(J + 64) & Rows.Count).End(xlUp)(2).Value = Cells(i, k).Value
        Next J
    End If
Next i

MsgBox "Report complete"

End Sub

[romperstomper]

FYI, your problem was that you were incrementing the value of d outside your if..end if clause, so it increased even if you didn't copy a row. You also don't need that Chr(64+j):

Private Sub CommandButton2_Click()
Dim i As Long, J As Long, d As Long
Dim k As String
d = 2
    For i = 4 To Range("M" & Rows.Count).End(xlUp).Row
        If Cells(i, 13).Value <= Sheets("Settings").Range("B2") Then
            For J = 1 To 5
               k = Choose(J, "A", "B", "F", "K", "M")
               Sheets("Renewals").Cells(d, J).Value = Cells(i, k).Value
            Next J
           d = d + 1
        End If
    Next i
MsgBox "Report complete"
End Sub

[Rogozhin]

Many thanks to StephenR and Romperstomper for the help - works perfectly! Thanks.