CODEWITHSUNDEEP
excelvba
gurpreet kaur
gurpreet kaur

Reputation: 23

Copy value of a particular cell and paste it to cell of next row

I want to copy the value of a particular cell of a row and paste it to an another cell of next row. Here is what I have so far.

for i= 2 to 26160
    If (Cells(i, 3) >= 99) Then

        Cells(i, 3).Select
        Selection.copy
        Cells(i, 4).Select 'error
        Selection.Paste    'error    
    end if    
next i

But my code is not working getting error in line 4 and 5?

Upvotes: 1

Views: 603

Answers (3)

iDevlop
iDevlop

Reputation: 25272

This sould be simple and FAST: 

Sub test()
    Dim c As Range
    Debug.Print Timer,
    For Each c In Range("C2:C26160")
        If c.Value >= 99 Then
            'copy to next cell
            c.Copy c.Offset(0, 1)
            'or copy to next col in other sheet
            c.Copy Sheet3.Range(c.Offset(0, 1).Address)
        End If
    Next c
    Debug.Print Timer

End Sub

Upvotes: 0

glh
glh

Reputation: 4972

Why not use a direct copy and avoid the clipboard via the below method?

set NewSheet = Sheets("Sheet1") 'New sheet name
j = 1 'start pasting in this row on new sheet
for i= 2 to 26160   
    If ActiveSheet.Cells(i, 3) >= 99 Then 
        NewSheet.Cells(j, 4) = ActiveSheet.Cells(i, 3)
        j = j + 1
    end if 
next i

Upvotes: 0

Santosh
Santosh

Reputation: 12353

You can combine the lines. Try below code. Avoid using select in your code. Why?

   'at beginning of proc
With Excel.Application
    .ScreenUpdating = False
    .Calculation = Excel.xlCalculationManual
    .EnableEvents = False
End With

'''
'your code

' updated as per comment
j = 1
For i = 2 To 26160
    If (Sheets("sheet2").Cells(i, 3) >= 99) Then
        Sheets("sheet2").Cells(i, 3).Copy Sheets("sheet3").Cells(j, 4)
        j = j + 1
    End If
Next i

'at end of proc
With Excel.Application
    .ScreenUpdating = True
    .Calculation = Excel.xlAutomatic
    .EnableEvents = True
End With

Upvotes: 1

Related Questions