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

Closed
Shaylene - Updated by Ambucias on 3/03/17 at 04:45 PM
vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 - Mar 5, 2017 at 05:44 PM
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!!
Related:

3 responses

vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Mar 5, 2017 at 04:22 AM
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.
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.
vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Mar 5, 2017 at 05:44 PM
Hello Shaylene,

Yes. Just remove line 12 from the code.

I hope that its all working for you.

Cheerio,
vcoolio.