Copy and paste a range of data from 1 worksheet to another based on a value

[Needinghlp]

Hello,

I have found lots of information on how to copy whole rows from 1 worksheet to another, but can't find something to meet my needs. I actually have 2 questions:
Question 1:
I have 2 sheets called 'sheet 1' and 'sheet 2'.
In sheet 1, data starts from row 3 and column C has a value of 'yes' or 'no'.
If column C has a value of 'yes' then I would like to copy the row from column a to column g, to sheet 2, pasting it in column a to g.
The code I have at the moment is below and works great if I want to copy the whole row - but I don't want to do that as it overwrites information columns h onwards in sheet 2.
Question 2:
At the moment I just look at the first 1000 rows. Is there a way I can have the code determine what the last row is?

Many thanks.


Private Sub CommandButton1_Click()

Dim c As Range
Dim j As Integer
Dim source As Worksheet
Dim target As Worksheet

'çhange worksheet designations as needed
Set source = ActiveWorkbook.Worksheets("Sheet1")
Set target = ActiveWorkbook.Worksheets("Sheet2")

j = 3 'start copying to row 3 in target sheet

For Each c In source.Range("c1:c1000")
'do 1000 rows
If c = "yes" Then
source.Rows(c.Row).Copy target.Rows(j)
j = j + 1

End If
Next c
ThisWorkbook.Save
MsgBox ("Copy and paste is complete")

End Sub

 

[mrshl9898]

Sounds like a Pivot Table might have also been a solution here.

Private Sub CommandButton1_Click()

Dim c As Range
Dim j As Integer
Dim lastrow As Long
Dim source As Worksheet
Dim target As Worksheet

'çhange worksheet designations as needed
Set source = ActiveWorkbook.Worksheets("Sheet1")
Set target = ActiveWorkbook.Worksheets("Sheet2")
lastrow = source.Cells(source.Rows.Count, "C").End(xlUp).Row

j = 3 'start copying to row 3 in target sheet

For Each c In source.Range("c1:c" & lastrow)
'do 1000 rows
If c = "yes" Then
source.Range("A" & c.Row & ":H" & c.Row).Copy target.Range("A" & j)
j = j + 1

End If
Next c
ThisWorkbook.Save
MsgBox ("Copy and paste is complete")

End Sub

 

[Needinghlp]

Th

Sounds like a Pivot Table might have also been a solution here.

Private Sub CommandButton1_Click()

Dim c As Range
Dim j As Integer
Dim lastrow As Long
Dim source As Worksheet
Dim target As Worksheet

'çhange worksheet designations as needed
Set source = ActiveWorkbook.Worksheets("Sheet1")
Set target = ActiveWorkbook.Worksheets("Sheet2")
lastrow = source.Cells(source.Rows.Count, "C").End(xlUp).Row

j = 3 'start copying to row 3 in target sheet

For Each c In source.Range("c1:c" & lastrow)
'do 1000 rows
If c = "yes" Then
source.Range("A" & c.Row & ":H" & c.Row).Copy target.Range("A" & j)
j = j + 1

End If
Next c
ThisWorkbook.Save
MsgBox ("Copy and paste is complete")

End Sub

Thanks so much - that's worked nicely. I was soooo close on a couple of aspects but just couldn't put it all together