Creating a formula If a cell is equal to or less than 7.00 [Closed]

Report
-
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
-
Hello!

I am looking for a formula that will do the following:


If a cell is equal to or less than 7.00, the entire row which contains said cell, should be copied to another sheet in the same workbook.

Any help is appreciated.

Thanks!!

3 replies

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Shaylene,

We'll need more information than what you have offered but in the meantime, try the following code assigned to a button:-

Sub Transfer()

Application.ScreenUpdating = False

With ActiveSheet
    .AutoFilterMode = False
    With Range("B1", Range("B" & Rows.Count).End(xlUp))
        .AutoFilter 1, "<=7"
        On Error Resume Next
        .Offset(1).EntireRow.Copy
        Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
        .Offset(1).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True
Sheet2.Select

End Sub


This code assumes that the values (<=7) are in Column B. Change this to suit your data set. The code also deletes the "used" data from Sheet 1 once it is transferred to Sheet 2.

I hope that this helps.

Cheerio,
vcoolio.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Thank you!!

Is there a way to take out the part of the code that "cuts" the data from the original spreadsheet? I need only need it to be copied.
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Shaylene,

Yes. Just remove line 12 from the code.

I hope that its all working for you.

Cheerio,
vcoolio.