creating a formula If a cell is equal to or less than 7.00Closed

Question

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!!

vcoolio · Answer

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.

Shaylene · Answer

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 · Answer

Hello Shaylene,
Yes. Just remove line 12 from the code.
I hope that its all working for you.
Cheerio,
vcoolio.

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

3 responses

Similar discussions

Subscribe To Our Newsletter!