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

Needinghlp

New Member
Joined
Feb 27, 2013
Messages
10
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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,619
Sounds like a Pivot Table might have also been a solution here.

VBA Code:
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

New Member
Joined
Feb 27, 2013
Messages
10
Th
Sounds like a Pivot Table might have also been a solution here.

VBA Code:
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,314
Messages
5,595,441
Members
413,991
Latest member
waterstone

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top