Search for last information in a range to copy and paste

Question

I have a code that searches for a value in another shett, after searching i want to copy what the original sheet has bellow in another cells, but i want to oly copy what has information. Then return to the value found and paste bellow last cell with information.

In the sample code the partida.value was found in sheets("bancos") cell = H6 I want to copy the info in Sheets("Bu") B7:C19 and its supposed to get pasted bellow sheets("bancos") G13:h13

Private Sub C1_Click()
    Dim Partida As String
    Dim Rng As Range


    Partida = Worksheets("BU").Range("c3").Value
    If Trim(Partida) <> "" Then
        With Sheets("Bancos").Rows("6:6")
            Set Rng = .Find(What:=Partida, after:=.Cells(.Cells.Count), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
            If Not Rng Is Nothing Then
                Worksheets("Bu").Activate
                ActiveSheet.Range("b7:c19").Select
                'i want to copy only the filled cells in the range (b7:c19); the filled cells in b and c
                Selection.Copy
                Application.Goto Rng, True

                'I want to paste in the last cells with information within the right and below cells from the "rng" found in cells G and H
            Else
                MsgBox "Not found"
            End If
        End With
    End If

End Sub

No error msg tho

No need to use `Activate` or `Select`: see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Is your question about how to paste? Because there's a `Copy` line, but no subsequent paste. — BigBen, Jul 04 '19 at 14:14
Yeah i was just refering how far i went :p but no, i dont know how to select only the information filled in the range b7:c19 and then return to rng and paste it in the last information 3 cells bellow and 1 left of rng. — Armas.Chuy, Jul 04 '19 at 14:17
How do you want it pasted? In a row or a column or something else? — SJR, Jul 04 '19 at 14:30
I want to paste only the filled information in b7:c19 in this case to g13:h13 but i want is to start searching 3 cells bellow and 1 left for the last information like in the example, start searching in g10:h10. — Armas.Chuy, Jul 04 '19 at 14:34
So the C3 value is found in H6 and you want to paste the cells in G10:H10 and down? That's 4 rows down, not 3. Are you always pasting the same range B7:C19? — SJR, Jul 04 '19 at 14:45
Yeah my bad :( and yeah its always the same, its a new format where people will fill it and then when they click in save it will copy and paste the information. And also it will allways search for value in C3 — Armas.Chuy, Jul 04 '19 at 14:50

score 0 · Accepted Answer · answered Jul 04 '19 at 15:13

Can you try this. It's untested, but should get you close at least.

Private Sub C1_Click()

Dim Partida As String
Dim Rng As Range, r1 As Range, r As Long, c As Long

Partida = Worksheets("BU").Range("c3").Value

If Trim(Partida) <> "" Then
    With Sheets("Bancos").Rows("6:6")
        Set Rng = .Find(What:=Partida, after:=.Cells(.Cells.Count), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
        If Not Rng Is Nothing Then
            r = Rng.Row + 4
            c = Rng.Column - 1
            For Each r1 In Worksheets("Bu").Range("b7:c19")
                If Len(r1) > 0 Then
                    .Cells(r, c + r1.Column - 2).Value = r1.Value
                    r = r + 1
                End If
            Next r1
        Else
            MsgBox "Not found"
        End If
    End With
End If

End Sub

Search for last information in a range to copy and paste

1 Answers1