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 3data 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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200906/1

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


On Jun 7, 10:37*am, "tkraju via OfficeKB.com" <u16627@uwe wrote:
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 3data *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

--
Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/ms-excel/200906/1

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
muddan madhu wrote:
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]
--
Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/ms-excel/200906/1

--
Message posted via http://www.officekb.com