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

Question

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?

You say **row** but you are only copying to the next column, is this correct? — glh, Apr 19 '13 at 07:14
Kindly 'close' the questions by marking correct answers. This is what people here like when helping others. It will help ppl with same problem to identify the correct solution :) — Santosh, May 28 '13 at 01:18

score 1 · Answer 1 · edited May 23 '17 at 12:04

1

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

edited May 23 '17 at 12:04

Community

1
1

answered Apr 19 '13 at 05:45

Santosh

12,175
4
41
72

this code is working but taking 1 to 2 min to execute..suggest me sum fast working code – gurpreet kaur Apr 19 '13 at 05:52
@gurpreetkaur i have updated the code. Put them to speed up the code. – Santosh Apr 19 '13 at 06:08
hey santosh, can u plz help me out with copying these cell values to the next sheet.i.e from sheet 2 to sheet 3 – gurpreet kaur Apr 19 '13 at 06:32
Just put the sheet name in front of cells. Example Sheets("Sheet3").cells(i,4) – Santosh Apr 19 '13 at 06:34
i want to paste the cell values in continuous row.how can i do dat – gurpreet kaur Apr 19 '13 at 06:42
What you mean by continuous row? Can you elaborate please. – Santosh Apr 19 '13 at 06:45
i meant dat my values are not pasting in contiguous row.i.e in consecutive cells. – gurpreet kaur Apr 19 '13 at 06:49
@gurpreetkaur Kindly refresh the page. I have updated code again :) – Santosh Apr 19 '13 at 06:51

glh · Answer 2 · 2013-04-19T07:19:19.560

0

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

edited Apr 19 '13 at 07:19

answered Apr 19 '13 at 07:09

glh

4,900
3
23
40

hey can somebody suggests me what should i use for matching certain names in a partiular column..like i hav 10 names in column1. in dat column1 i need to check a particular name ,..ie whether rahul is dere in column1 or not...what should i use lookup,vlookup or hlookup? – gurpreet kaur May 15 '13 at 06:06

iDevlop · Answer 3 · 2013-04-19T07:49:08.920

0

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

edited Apr 19 '13 at 07:49

answered Apr 19 '13 at 07:35

iDevlop

24,841
11
90
149

hey can somebody suggests me what should i use for matching certain names in a partiular column..like i hav 10 names in column1. in dat column1 i need to check a particular name ,..ie whether rahul is dere in column1 or not...what should i use lookup,vlookup or hlookup? – gurpreet kaur May 15 '13 at 05:38
1) better to ask it as a new question 2) mark THIS question as replied if it is 3) you can use vlookup or hlookup or INDEX() or COUNTIF() – iDevlop May 15 '13 at 09:22

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

3 Answers3