Copy IF condition exists

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Office_Novice - 23 Jan 2008 02:11 GMT

Is it possiable to Copy and paste if a specific condition exists?

Heres the problem in column "F" there is a number. If that number is less
then 61 i would like to move the entire row to another sheet. also the range
will change with the data. can this be done?

Reply to this Message

Otto Moehrbach - 23 Jan 2008 02:46 GMT

Something like:
If Range("F5").Value<61 Then
'Your code to move/copy whatever you want.
End If
You will probably need more than that if you want to "move" the entire row
rather than just copy it.
HTH Otto

> Is it possiable to Copy and paste if a specific condition exists?
>
> Heres the problem in column "F" there is a number. If that number is less
> then 61 i would like to move the entire row to another sheet. also the
> range
> will change with the data. can this be done?

Reply to this Message

Office_Novice - 23 Jan 2008 04:19 GMT

Well Thats a good start thanks, if i could bother you for one more? Rather
then spend the rest of my life typing this over and over
Sub newone()
If Range("F1").Value < 61 Then
Sheets("Sheet1").Select
Rows("1:1").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
End If
If Range("F2").Value < 61 Then
Rows("2:2").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
End If
Could this be put in a loop to Do....Until There are no numbers < 61?

> Something like:
> If Range("F5").Value<61 Then
[quoted text clipped - 9 lines]
> > range
> > will change with the data. can this be done?

Reply to this Message

Otto Moehrbach - 23 Jan 2008 17:08 GMT

Maybe something like this: HTH Otto
Sub newone()
Dim RngColF As Range
Dim i As Range
Dim Dest As Range
Sheets("Sheet1").Select
Set RngColF = Range("F1", Range("F" & Rows.Count).End(xlUp))
With Sheets("Sheet2")
Set Dest = .Range("A1")
End With
For Each i In RngColF
If i.Value < 61 Then
i.EntireRow.Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End Sub

> Well Thats a good start thanks, if i could bother you for one more? Rather
> then spend the rest of my life typing this over and over
[quoted text clipped - 32 lines]
>> > range
>> > will change with the data. can this be done?

Reply to this Message

Office_Novice - 23 Jan 2008 18:04 GMT

So Far so Good thanks Otto!

> Maybe something like this: HTH Otto
> Sub newone()
[quoted text clipped - 49 lines]
> >> > range
> >> > will change with the data. can this be done?

Reply to this Message

Otto Moehrbach - 23 Jan 2008 19:46 GMT

So far so good?? You mean there's more? Otto

> So Far so Good thanks Otto!
>
[quoted text clipped - 53 lines]
>> >> > range
>> >> > will change with the data. can this be done?

Reply to this Message

Office_Novice - 24 Jan 2008 19:00 GMT

Isnt there always more? Thanks for you rhelp.

> So far so good?? You mean there's more? Otto
> > So Far so Good thanks Otto!
[quoted text clipped - 54 lines]
> >> >> > range
> >> >> > will change with the data. can this be done?

Reply to this Message