VBA copy rows that meet criteria to another sheet pasting only values

Question

I would like to modify this macro to paste the copied rows with their original formatting and only their values as the rows being copied have formulas in them. I tried placing PasteSpecial xlPasteValues after Rows(j+6) but that did not do the trick.

    Sub customcopy()
    Dim strsearch As String, lastline As Integer, tocopy As Integer

    strsearch = CStr(InputBox("enter the string to search for"))
    lastline = Range("A65536").End(xlUp).Row
    j = 1

    For i = 1 To lastline
       For Each c In Range("C" & i & ":Z" & i)
          If InStr(c.Text, strsearch) Then
               tocopy = 1
           End If
        Next c
        If tocopy = 1 Then
             Rows(i).Copy Destination:=Sheets("Sheet2").Rows(j + 6)
             j = j + 1
        End If
    tocopy = 0
    Next i

    End Sub

you might want to add a line like exit for , just after tocopy=1. because if the copy condition is already met, why bother looping around ? Also, you did not declare J. tocopy,lastline,J as Long wouldn't harm. — Patrick Lepelletier

Gary's Student Gary's Student · Accepted Answer · 2014-05-10T13:52:40

Try:

Sub customcopy()
    Dim strsearch As String, lastline As Long, tocopy As Long
    strsearch = CStr(InputBox("enter the string to search for"))
    lastline = Range("A65536").End(xlUp).Row
    j = 1
    For i = 1 To lastline
       For Each c In Range("C" & i & ":Z" & i)
          If InStr(c.Text, strsearch) Then
               tocopy = 1
           End If
        Next c
        If tocopy = 1 Then
             Rows(i).Copy
             Sheets("Sheet2").Rows(j + 6).PasteSpecial (xlValues)
             Sheets("Sheet2").Rows(j + 6).PasteSpecial (xlFormats)
             j = j + 1
        End If
        tocopy = 0
    Next i
End Sub

VBA copy rows that meet criteria to another sheet pasting only values

3 Answers