Home » Blogs » tigershark45_can's blog

IF VALUE EQUALS X THEN COPY PASTE ROW TO NEW SHEET

Submitted by tigershark45_can on 3 May, 2011 - 15:30

Help!.. I would like to know how to copy paste from one sheet to another within the same file using the IF function.
Basically, looking a thousands of columns, if I want to copy specific rows (A15:K15), (B15:K15) etc.. of "sheet 1" to "sheet 2" only if (employee number) D15 = 11 (of sheet 1).

I would like the rows to follow in "sheet 2" and not appear at row 1500 if there is only 20 rows that were copied.

Does this make any sense? If VBA is needed... what would the code look like?

Thanks,

Tigershark

»
Nick's picture

filter

Submitted by Nick on 4 May, 2011 - 12:19.
  • copy all the data across to sheet2.
  • use Autofilter to filter out the rows you don't want
  • delete them
  • turn off filter, and you're left with the ones you do want

more on autofilter:

excelexperts.com/Teach-Yourself-Excel-Lesson-21-Filter-Data

»

Your VBA question

Submitted by Robert on 4 May, 2011 - 22:36.

If you do want to use VBA, the code could look something like this:
 

Sub CopyRows()
 
strEnr = InputBox("Enter employee number", "Employee number")
Enr = CInt(strEnr)
 
Set s1 = Worksheets(1)
n = Application.CountA(s1.Columns(1))
 
i = 0
 
For j = 1 To n
    If Worksheets(1).Cells(j, 15).Value = Enr Then
        i = i + 1
        Set r = Worksheets(1).Rows(j)
        r.Copy Destination:=Worksheets(2).Rows(i)
    End If
Next j
 
End Sub
»