Copy selected rows into another worksheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

What do I do if I want to copy a whole row into a new worksheet if it fulfils a criteria in an if-sentence?

Regard
dSb
 
Is your IF part of a VBA macro, or is it already in the worksheet and will
change with the correct input?
When you say "new worksheet", do you mean you want to create a brand-new
worksheet every time you get an IF-statement match and paste just that one
row there? Or do you have an existing worksheet that is collecting all rows
which match your criteria? Or create a worksheet on the first match, and
paste all matches there?

Ed

dSb said:
Hello!

What do I do if I want to copy a whole row into a new worksheet if it
Click to expand...
fulfils a criteria in an if-sentence??
 
Option explicit
sub testme01()

dim myCell as range
dim destCell as range

with worksheets("receivingWks")
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

with worksheets("sendingWks")
set mycell = .range("b9")
if mycell.value < 100 then
mycell.entirerow.copy _
destination:=destcell
end if
end with

End sub

And if I wanted to loop through the rows of one worksheet and copy some of the
rows over to a different worksheet.

Option Explicit
Sub testme02()

Dim myCell As Range
Dim destCell As Range
Dim iRow As Long

With Worksheets("receivingWks")
Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With Worksheets("sendingWks")
For iRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
If .Cells(iRow, "B").Value < 100 Then
.Rows(iRow).Copy _
Destination:=destCell
Set destCell = destCell.Offset(1, 0)
End If
Next iRow

End Sub


Note that both of these routines rely data in column A. (Otherwise, you'll have
to find some other way to determine the "destination cell" and the number of
rows to loop through.)
 
oops. I forgot an End With at the bottom of the second routine and it's
probably better to check to see if the value is numeric before I do a numeric
compare. (Some versions of excel were picky about that--others were less
picky.)

Option Explicit
Sub testme01()

Dim myCell As Range
Dim destCell As Range
Dim iRow As Long

With Worksheets("receivingWks")
Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With Worksheets("sendingWks")
For iRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
If IsNumeric(.Cells(iRow, "B")) Then
If .Cells(iRow, "B").Value < 100 Then
.Rows(iRow).Copy _
Destination:=destCell
Set destCell = destCell.Offset(1, 0)
End If
End If
Next iRow
End With

End Sub

(and the same check (Isnumeric) should be added to the first routine, too.
(Sorry)
 
Back
Top