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
1345
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 20, 2022
- 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!!

3 replies

vcoolio
Posts
1345
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 20, 2022
249
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.
1
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.
0
vcoolio
Posts
1345
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 20, 2022
249
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.
0