Copy cells to different sheet if value found

[F]

I have an Excel 2010 workbook with multiple worksheets.

Worksheet 'one' has column B holding values such as ',1,12,20,'
(starting at row 2) with adjacent cells in column C holding textual
comments relating to the entries in column B.

I need to search through column B and find the first cell holding ',1,'
and then copy the adjacent cell in column C to column D in worksheet
'two'. The search then needs to continue to find the next occurrence of
',1,' in B and copy the corresponding cell in column C to the next row
in column D of worksheet 'two'.

Some of the cells in column B of worksheet 'one' do not hold ',1,' so I
would prefer it if they did not provoke blank rows in worksheet 'two'.

Any help would be gratefully received!

TIA

 

[venkat1926]

had you given small extract of data it would have been helpful to write a macro

suppose sample data is in A1 to D8 is as follows

hdng1 hdng2 hdng3 hdng4
1.2.20 a
1.3.20 s
2.1.33 d
6.1.22 f
1.56.22 g
3.5.22 j
4.5.23 k




on this sample data run this macro and if it is ok modify to suit your data


Sub test()
Dim r As Range
Worksheets("sheet2").Cells.Clear
Worksheets("sheet1").Activate
Set r = Range("a1").CurrentRegion
'MsgBox r.Address
r.AutoFilter field:=2, Criteria1:="=1*"
Dim filt As Range
Set filt = r.Offset(1, 0).Resize(r.Rows.Count - 1).Columns("c:c").SpecialCells(xlCellTypeVisible)
'MsgBox filt.Address
filt.Copy
With Worksheets("sheet2")
..Range("D2").PasteSpecial
End With
ActiveSheet.AutoFilterMode = False
Application.CutCopyMode = False
End Sub

mine excel 2007 windows 7