CODEWITHSUNDEEP
vbaexcel
Sir DrinksCoffeeALot
Sir DrinksCoffeeALot

Reputation: 633

Copy single cell from worksheet to another worksheet using VBA

This is a search in VBA that copies a selected cell from one worksheet to another.

Sub uredi()
    Application.ScreenUpdating = False

    Dim ime As String
    Dim prezime As String
    Dim red As Integer
    Dim k As String

    ime = Sheets("Evidencija").Range("C7").Value
    prezime = Sheets("Evidencija").Range("C8").Value
    red = Sheets("Baza podataka").Range("F10000").End(xlUp).Row

    Sheets("Baza podataka").Select
    For i = 3 To red
        If Cells(i, 6) = ime And Cells(i, 7) = prezime Then

        ' this is where i get the error
        Sheets("Baza podataka").Range(Cells(i, 2)).Copy 

        Sheets("Evidencija").Range("C2").PasteSpecial (xlPasteAll)
      End If
    Next i

    Sheets("Evidencija").Select
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Upvotes: 1

Views: 3105

Answers (2)

Davesexcel
Davesexcel

Reputation: 6984

This will do it,

    Sub Button1_Click()
    Dim ws As Worksheet, sh As Worksheet
    Dim Rws As Long, Rng As Range, c As Range
    Dim ime As String
    Dim prezime As String
    Dim red As Integer

    Set ws = Sheets("Evidencija")
    Set sh = Sheets("Baza podataka")

    With sh
        Rws = .Cells(Rows.Count, "F").End(xlUp).Row
        Set Rng = .Range(.Cells(3, "F"), .Cells(Rws, "F"))
    End With

    ime = ws.Range("C7").Value
    prezime = ws.Range("C8").Value

    For Each c In Rng
        If c = ime And c.Offset(0, 1) = prezime Then
            c.Offset(0, -3).Copy ws.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next c

End Sub

This will copy and past to the next empty cell in column A, you can change the paste code to an actual range if you want.

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

First replace:

Sheets("Baza podataka").Range(Cells(i, 2)).Copy

with:

Sheets("Baza podataka").Cells(i, 2).Copy

and then continue debugging.

Upvotes: 1

Related Questions