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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,630
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,755
Messages
5,597,929
Members
414,193
Latest member
bb60

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