copy matching value rows to other sheet

[tkraju via OfficeKB.com]

I have 3 w/sheets
w/sheet 1 >>>> cell A1=May-09
w/sheet 2 >>>data spread from A1 to C 5 (where Col c rows contain months
(May-09,Jun-09,Apr-09)
w/sheet 3>>>>data already in A1 to C4
I need a macro that matches w/sheet1!A1 value with w/sheet2 column C values,
copy all the matching rows to end row of w/sheet3.please don't advice me to
use filter.
Example;
W/sheet 1:
A B C
1 May-09

W/sheet 2:
A B C
1.john $500 Apr-09
2.Mary $459 May-09
3.kathy $ 500 Apr-09
4.Rahul $450 May-09
5.george $324 Apr-09


Output of macro should be like this;
W/sheet3:
A B C
5. Mary $459 May-09
6. Rahul $450 May-09

 

[muddan madhu]

try this

Sub get_data()
Dim i As Integer
Dim rng As Integer, rng1 As Integer

With Application
.ScreenUpdating = False

Set sht = Sheet2
Set sht1 = Sheet3

rng = sht.Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To rng

rng1 = sht1.Cells(Rows.Count, "A").End(xlUp).Row

If Format(Sheet1.Cells(1, "A"), "mmm-yy") = Format(sht.Cells(i, "C"),
"mmm-yy") Then
sht.Rows(i).Copy
sht1.Activate
Cells(rng1 + 1, "a").Select
ActiveSheet.Paste
End If
Next i

.ScreenUpdating = True
.CutCopyMode = False
End With

End Sub

 

[tkraju via OfficeKB.com]

Thank you Madhu,I have tried this,the routine is copying matching rows twice.
I don't understand why the code is running .can you please correct it ?
twice

try this

Sub get_data()
Dim i As Integer
Dim rng As Integer, rng1 As Integer

With Application
.ScreenUpdating = False

Set sht = Sheet2
Set sht1 = Sheet3

rng = sht.Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To rng

rng1 = sht1.Cells(Rows.Count, "A").End(xlUp).Row

If Format(Sheet1.Cells(1, "A"), "mmm-yy") = Format(sht.Cells(i, "C"),
"mmm-yy") Then
sht.Rows(i).Copy
sht1.Activate
Cells(rng1 + 1, "a").Select
ActiveSheet.Paste
End If
Next i

.ScreenUpdating = True
.CutCopyMode = False
End With

End Sub

I have 3 w/sheets
w/sheet 1 >>>>  cell A1=May-09

[quoted text clipped - 25 lines]