Find a value from a sheet and copy rows from sheet 1 to sheet2

Question

I try this code now, and give me a error, in destination. I dont know what im doing wrong. i try several times, a quite diferent things now.

Sub Find_First()

    Dim FindString As String
    Dim Rng As Range
    Dim RowCnt As Long

    FindString = Sheets("sheet1").Range("F5").Value

    If Trim(FindString) <> "" Then
        With Sheets("sheet2").Range("A:A")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then

            RowCnt = Rng.Row

            Worksheets("sheet1").Range("p13:af13").Copy Destination:=Worksheets("sheet2").Range(Cells(RowCnt, 1))




                Application.Goto Rng, True
            Else
                MsgBox "Nothing found"
            End If
        End With
    End If
End Sub

i need a macro, that will search a value in cell f5 in sheet1, and search in column A in sheet2. if that value is found. o want to copy a range of cells from sheet1 ( P13:AF13) to the row that the value was found in sheet 2 — jmonets s, Apr 11 '17 at 08:59
What have you tried? What part of your code is not working as you expect it to? — YowE3K, Apr 11 '17 at 09:15
If you "need a macro" you can either ask for it or pay for it. Since you haven't asked I presume you want to pay. Unfortunately there is no "pay for code" on this site. You might try elance.com. — Variatus, Apr 11 '17 at 09:22
What @Variatus is trying to say is that your question does not meet the quality standards of this site. Please read [ask] and edit your question to meet the standard. Failure to do so will likely result in heavy down voting and closure of the question. Repeatedly posting poorly received questions will eventually result in a question ban — chris neilsen, Apr 11 '17 at 09:55
I see it. o just edit the post. Is it better now? i really need your help — jmonets s, Apr 11 '17 at 10:12
@jmonetss well done for trying to improve. I think you need a bit more explanation of what you want to achieve. In the meantime, you should avoid Select in your Code. [see here for some guidance](http://stackoverflow.com/q/10714251/445425) — chris neilsen, Apr 11 '17 at 10:31
i need to search a value in a column, if that value is found, i need to copy a range of cell to that row — jmonets s, Apr 11 '17 at 10:50
i rewrite my code, is that making anysense? im in desperate mod — jmonets s, Apr 11 '17 at 13:18

score 0 · Answer 1 · answered Apr 11 '17 at 18:17

Let's say you want to search for the character 'X' in Column A of Sheet1 and if found, copy and paste the row to Sheet2, then run the script below.

Sub LastRowInOneColumn()
   Dim LastRow As Long
   Dim i As Long, j As Long

   'Find the last used row in a Column: column A in this example
   With Worksheets("Sheet1")
      LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
   End With

   'MsgBox (LastRow)
   'first row number where you need to paste values in Sheet1'
   With Worksheets("Sheet2")
      j = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
   End With

   For i = 1 To LastRow
       With Worksheets("Sheet1")
           If .Cells(i, 1).Value = "X" Then
               .Rows(i).Copy Destination:=Worksheets("Sheet2").Range("A" & j)
               j = j + 1
           End If
       End With
   Next i
End Sub

Is that what you want?

Find a value from a sheet and copy rows from sheet 1 to sheet2

1 Answers1