Extract Data From another sheet using VBA

[Joy Cooper]

I have a data sheet in a workbook which I need to extract all the rows where Col G is equal to "Y" and insert the adjacent Cells (Cols A:F) into another sheet.

Is there a piece of VBA code that can do this please?

Many thanks

[Simon Lloyd]

We would need to see a sample workbook to give you a workable solution but a general code would be:[VBA]Sub copy_over_data()
Dim Rng As Range, MyCell As Range
Set Rng = Sheets("Sheet1").Range("G1:G" & Sheets("Sheet1").Range("G" & Rows.Count).End(xlUp).Row)
For Each MyCell In Rng
If LCase(MyCell.Value) = LCase("Y") Then
MyCell.Offset(0,1).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next MyCell
End Sub[/VBA]

[Joy Cooper]

Thanks for your response.

I have tried to adapt the code you provided, which unfortunately didn't work as follows:

[vba]
Sub copy_over_data()
Dim Rng As Range, MyCell As Range
Set Rng = Sheets("Data(Timesheets)").Range("G1:G" & Sheets("Data(Timesheets)").Range("G" & Rows.Count).End(xlUp).Row)
For Each MyCell In Rng
If LCase(MyCell.Value) = LCase("Y") Then
MyCell.Offset(0, 1).Copy Destination:=Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next MyCell
End Sub
[/vba]

I've attached a workbook with a sample set of data.

To elaborate - I need to extract all the information in Cols A:F where the value of the adjacent cell in Col G is "Y". This extract then needs to be placed in Sheet1.

Many thanks again.