CODEWITHSUNDEEP
excelvba
HKS
HKS

Reputation: 1003

How to paste in successive rows

Sub NSV_LINK()
Dim cell As Range
Dim Rng As Range
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
For Each cell In Rng
If cell.Value = "Hemanta" Then cell.EntireRow.Copy Sheets(2).Cells(1, 1)
Next cell
End Sub

In the code above, I want the macro to copy and paste values in successive rows in sheet 2. However, I have hard coded the destination cell i.e, the value gets pasted at A1. How do I write the cell destination, so that the values get pasted in successive rows? Cells(i, 1)...Something like this. And then i takes a range from, let's say 1 to 20. How do I write this in code?

Upvotes: 0

Views: 416

Answers (2)

DisplayName
DisplayName

Reputation: 13386

you can adapt the same technique you already used in Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)) to make destination range dynamic:

Sub NSV_LINK()
    Dim cell As Range, Rng As Range

    Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    For Each cell In Rng
        If cell.Value = "Hemanta" Then cell.EntireRow.Copy Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1) ' make destination range dynamic to target sheeet column A first not empty cell after last not empty one
    Next cell
End Sub

Upvotes: 1

patel
patel

Reputation: 440

you need a counter and you have to increment it

Sub NSV_LINK()
Dim cell As Range, Rng As Range, r As Long
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
r = 1
For Each cell In Rng
    If cell.Value = "Hemanta" Then
        cell.EntireRow.Copy Sheets(2).Cells(r, 1)
        r = r + 1
    End If
Next cell
End Sub

Upvotes: 1

Related Questions