Pasting Selected Rows To Another Sheet

[RoyinUK2000]

I would like to check a worksheet column and paste a row if it has an f in the column i am checking. Can any of the guru's help me to do this in vb


Thanks
Roy

 

[Dave Hawley]

Hi Roy


Here is some very quick code to narrow down your range the cells with an "f" anywhere in them. It assumes the data is in Column "A" and Column "B" is empty

Sub DoIt()
Dim rRange As Range
Dim rCell As Range

Range("A1", Range("A65536").End(xlUp)) _
.Offset(0, 1).FormulaR1C1 = "=FIND(""f"",RC[-1])"

Set rRange = Range("B1", Range("B65536").End(xlUp)).Offset(0, 1)
rRange = rRange.Value

Set rRange = rRange.SpecialCells(xlCellTypeConstants, xlNumbers)
rRange.Clear

For Each rCell In rRange
'rCell.EntireRow 'DO WHAT YOU WANT.
Next rCell

 

[RoyinUK2000]

That doesn't do what i want. I have several columns of data. I want to search column h and if column h contains "f" then i want to copy this to another sheet at the next empty row on the other sheet.

Any help appreciated.
Roy

 

[Dave Hawley]

Hi Roy

You have not even tried have you? Your post above is 7 minutes after my first reply!

Make sure Column I is empty (insert a Column if it's not)

Sub DoIt()
Dim rRange As Range
Dim rCell As Range

Range("H1", Range("H65536").End(xlUp)) _
.Offset(0, 1).FormulaR1C1 = "=FIND(""f"",RC[-1])"

Set rRange = Range("I1", Range("I65536").End(xlUp)).Offset(0, 1)
rRange = rRange.Value

Set rRange = rRange.SpecialCells(xlCellTypeConstants, xlNumbers)


For Each rCell In rRange
rCell.EntireRow.Copy Destination:= _
Sheets("Sheet1").Range("A65536").End(xlUp).Cells(2, 1)
Next rCell
rRange.Clear
End If

 

[RoyinUK2000]

I have tried this code but first it said no if block for end if. I removed the end if and i inserted a blank column i. The code then proceeded to copy all the rows into my new sheet??????.
Any ideas what is going wrong

Thanks m8
Roy

 

[Dave Hawley]

The original code will look for the letter "f" in the cell, so if the cell contained the word "Offle" it would assume a match. I now take it you want only cells that contain a single "f" and nothing else? If so try this.

Sub DoIt()
Dim rRange As Range
Dim rCell As Range

Range("H1", Range("H65536").End(xlUp)) _
.Offset(0, 1).FormulaR1C1 = "=IF(RC[-1]=""f"",1,NA())"

Set rRange = Range("I1", Range("I65536").End(xlUp)).Offset(0, 1)
rRange = rRange.Value

Set rRange = rRange.SpecialCells(xlCellTypeConstants, xlNumbers)


For Each rCell In rRange
rCell.EntireRow.Copy Destination:= _
Sheets("Sheet1").Range("A65536").End(xlUp).Cells(2, 1)
Next rCell
rRange.Clear

End Sub

 

[RoyinUK2000]

I have tried the code again but i am getting an error at
Set rRange = rRange.SpecialCells(xlCellTypeConstants, xlNumbers)

If i delete this line then the code runs but again it copies the whole of the sheet, not just the rows containing f.

Any idea's !!!!!
Thanks
Roy

 

[RoyinUK2000]

Come on guys i am really struggling with this problem, have tried all sorts, can anybody give me any suggestions / idea's.

Cheerz
Roy.

 

[RoyinUK2000]

^^^^bump^^^^

 

[RoyinUK2000]

Come on guys i am really struggling with this problem, have tried all sorts, can anybody give me any suggestions / idea's.

Cheerz
Roy.