I need code to copy all the rows that contain value "May-09"(this is variable
say sheet3!a1 value) of sheet1 and paste them to last row of sheet2

Raju

The below macro should copy the rows from Sheet1 to Sheet2 if Col A of
Sheet1 matches with the entry given in Sheet3A1. Sheet1 Col A and Sheet3 cell
A1 entries should be in Date format...

Sub Macro()
Dim wb As Workbook
Dim varFind As Variant
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lngRow As Long
Dim lngLastRow1 As Long, lngLastRow2 As Long

Set wb = ActiveWorkbook
Set ws1 = wb.Sheets("Sheet1")
Set ws2 = wb.Sheets("Sheet2")
varFind = Format(wb.Sheets("Sheet3").Range("A1"), "mmmyy")

lngLastRow1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
lngLastRow2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row

For lngRow = 1 To lngLastRow1
If Format(ws1.Range("A" & lngRow), "mmmyy") = "May09" Then
ws1.Rows(lngRow).Copy ws2.Rows(lngLastRow2 + 1)
lngLastRow2 = lngLastRow2 + 1
End If
Next
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"TUNGANA KURMA RAJU" wrote:

I need code to copy all the rows that contain value "May-09"(this is variable
say sheet3!a1 value) of sheet1 and paste them to last row of sheet2

Thank you Jacob,the macro is giving slight wrong output.Its copying matching
rows twice to Sheet2.can you please correct it,please.

"Jacob Skaria" wrote:

Raju

The below macro should copy the rows from Sheet1 to Sheet2 if Col A of
Sheet1 matches with the entry given in Sheet3A1. Sheet1 Col A and Sheet3 cell
A1 entries should be in Date format...

Sub Macro()
Dim wb As Workbook
Dim varFind As Variant
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lngRow As Long
Dim lngLastRow1 As Long, lngLastRow2 As Long

Set wb = ActiveWorkbook
Set ws1 = wb.Sheets("Sheet1")
Set ws2 = wb.Sheets("Sheet2")
varFind = Format(wb.Sheets("Sheet3").Range("A1"), "mmmyy")

lngLastRow1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
lngLastRow2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row

For lngRow = 1 To lngLastRow1
If Format(ws1.Range("A" & lngRow), "mmmyy") = "May09" Then
ws1.Rows(lngRow).Copy ws2.Rows(lngLastRow2 + 1)
lngLastRow2 = lngLastRow2 + 1
End If
Next
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"TUNGANA KURMA RAJU" wrote:

I need code to copy all the rows that contain value "May-09"(this is variable
say sheet3!a1 value) of sheet1 and paste them to last row of sheet2

Can you just confirm as I dont see an error.
--
If this post helps click Yes
---------------
Jacob Skaria


"TUNGANA KURMA RAJU" wrote:

Thank you Jacob,the macro is giving slight wrong output.Its copying matching
rows twice to Sheet2.can you please correct it,please.

"Jacob Skaria" wrote:

Raju

The below macro should copy the rows from Sheet1 to Sheet2 if Col A of
Sheet1 matches with the entry given in Sheet3A1. Sheet1 Col A and Sheet3 cell
A1 entries should be in Date format...

Sub Macro()
Dim wb As Workbook
Dim varFind As Variant
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lngRow As Long
Dim lngLastRow1 As Long, lngLastRow2 As Long

Set wb = ActiveWorkbook
Set ws1 = wb.Sheets("Sheet1")
Set ws2 = wb.Sheets("Sheet2")
varFind = Format(wb.Sheets("Sheet3").Range("A1"), "mmmyy")

lngLastRow1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
lngLastRow2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row

For lngRow = 1 To lngLastRow1
If Format(ws1.Range("A" & lngRow), "mmmyy") = "May09" Then
ws1.Rows(lngRow).Copy ws2.Rows(lngLastRow2 + 1)
lngLastRow2 = lngLastRow2 + 1
End If
Next
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"TUNGANA KURMA RAJU" wrote:

I need code to copy all the rows that contain value "May-09"(this is variable
say sheet3!a1 value) of sheet1 and paste them to last row of sheet2

yes Sir,
Its copying twice or may be looping twice.
"Jacob Skaria" wrote:

Can you just confirm as I dont see an error.
--
If this post helps click Yes
---------------
Jacob Skaria


"TUNGANA KURMA RAJU" wrote:

Thank you Jacob,the macro is giving slight wrong output.Its copying matching
rows twice to Sheet2.can you please correct it,please.

"Jacob Skaria" wrote:

Raju

The below macro should copy the rows from Sheet1 to Sheet2 if Col A of
Sheet1 matches with the entry given in Sheet3A1. Sheet1 Col A and Sheet3 cell
A1 entries should be in Date format...

Sub Macro()
Dim wb As Workbook
Dim varFind As Variant
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lngRow As Long
Dim lngLastRow1 As Long, lngLastRow2 As Long

Set wb = ActiveWorkbook
Set ws1 = wb.Sheets("Sheet1")
Set ws2 = wb.Sheets("Sheet2")
varFind = Format(wb.Sheets("Sheet3").Range("A1"), "mmmyy")

lngLastRow1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
lngLastRow2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row

For lngRow = 1 To lngLastRow1
If Format(ws1.Range("A" & lngRow), "mmmyy") = "May09" Then
ws1.Rows(lngRow).Copy ws2.Rows(lngLastRow2 + 1)
lngLastRow2 = lngLastRow2 + 1
End If
Next
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"TUNGANA KURMA RAJU" wrote:

I need code to copy all the rows that contain value "May-09"(this is variable
say sheet3!a1 value) of sheet1 and paste them to last row of sheet2

Hi Raju

There is something wrong in the way you are trying. Below is the test i did.
I would really like to what is going wrong, Please try and feedback

1. Open new workbook. Launch VBE using Alt+F11. From menu Insert a module
and paste the code.

2. On sheet1 Col A and B I have put the below values. Col A is in date
format inserted and edited using (Ctrl+Semicolon).

6/8/2009 0
6/8/2009 0
5/8/2009 1
5/8/2009 2
6/8/2009 0
6/8/2009 0

3. In Sheet 3 Cell A1 I have placed a date which is in May 5/8/2009. I
expected the two records to be copied to Sheet2; the values of which in ColB
are 1 and 2

4. Run the macro and see.
--
If this post helps click Yes
---------------
Jacob Skaria


"TUNGANA KURMA RAJU" wrote:

yes Sir,
Its copying twice or may be looping twice.
"Jacob Skaria" wrote:

Can you just confirm as I dont see an error.
--
If this post helps click Yes
---------------
Jacob Skaria


"TUNGANA KURMA RAJU" wrote:

Thank you Jacob,the macro is giving slight wrong output.Its copying matching
rows twice to Sheet2.can you please correct it,please.

"Jacob Skaria" wrote:

Raju

The below macro should copy the rows from Sheet1 to Sheet2 if Col A of
Sheet1 matches with the entry given in Sheet3A1. Sheet1 Col A and Sheet3 cell
A1 entries should be in Date format...

Sub Macro()
Dim wb As Workbook
Dim varFind As Variant
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lngRow As Long
Dim lngLastRow1 As Long, lngLastRow2 As Long

Set wb = ActiveWorkbook
Set ws1 = wb.Sheets("Sheet1")
Set ws2 = wb.Sheets("Sheet2")
varFind = Format(wb.Sheets("Sheet3").Range("A1"), "mmmyy")

lngLastRow1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
lngLastRow2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row

For lngRow = 1 To lngLastRow1
If Format(ws1.Range("A" & lngRow), "mmmyy") = "May09" Then
ws1.Rows(lngRow).Copy ws2.Rows(lngLastRow2 + 1)
lngLastRow2 = lngLastRow2 + 1
End If
Next
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"TUNGANA KURMA RAJU" wrote:

I need code to copy all the rows that contain value "May-09"(this is variable
say sheet3!a1 value) of sheet1 and paste them to last row of sheet2