PDA

View Full Version : Solved: Copy specific rows



ulfal028
06-09-2006, 01:12 AM
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.

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

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

geekgirlau
06-09-2006, 01:30 AM
Why not use a filter rather than looping?


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")


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

ulfal028
06-09-2006, 02:02 AM
Yes, that worked. Thank you!

andre_v_r
06-09-2006, 02:03 AM
'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