Solved: Copy specific rows

[ulfal028]

Hi,
I'm trying to copy all rows (cell cols B:J) from sheet "INDATA" that have column J value = "FR" to sheet "FR". Can't manage getting it to work properly, appreciate some help.

[vba] Dim i As Long
Dim LastRow As Long
Dim Row As Long
Dim Cel As Range

LastRow = Sheets("MVDATA").Range("J65536").End(xlUp).Row

Row = 2

For i = 2 To LastRow

If Sheets("MVDATA").Range("J" & i).Text = "FR" Then
Sheets("MVDATA").Range("B" & Cel.Row & ":J" & Cel.Row).Copy _
Destination:=Sheets("FR").Range("B" & Row)
End If
Row = Row + 1
Next i[/vba]

Edited 9-Jun-06 by geekgirlau. Reason: insert line breaks in code

[geekgirlau]

Why not use a filter rather than looping?

[vba]
Sheets("MVDATA").Select
Sheets("FR").Cells.Clear

' show all the records
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If

' filter for 'FR' in column J
Selection.AutoFilter Field:=10, Criteria1:="FR"
Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Select
Selection.SpecialCells(xlCellTypeVisible).Copy Sheets("FR").Range("A1")
[/vba]

The other alternative would be to use the advanced filter and copy to a new location.

[ulfal028]

Yes, that worked. Thank you!

[andre_v_r]

[VBA]
'Cel' was not defined the way you had it written
'plus Row should be increased only if a value is copied
Sub doCopyExample()

Dim i As Long
Dim LastRow As Long
Dim Row As Long
Dim Cel As Range

LastRow = Sheets("MVDATA").Range("J65536").End(xlUp).Row

Row = 2

For i = 2 To LastRow
Set Cel = Sheets("MVDATA").Range("J" & i)
If Cel.Text = "FR" Then
Sheets("MVDATA").Range("B" & Cel.Row & ":J" & Cel.Row).Copy _
Destination:=Sheets("FR").Range("B" & Row)
Row = Row + 1
End If

Next i
End Sub

[/VBA]