How to use VBA to copy rows with a “No” value in the last column and paste the values into another sheet?

kbay808
kbay808 used Ask the Experts™
on
Key Notes:
1) All of rows with the “No” indicator will always be grouped together and at the bottom
2) One of the columns for the source data contains formulas
In the example spreadsheet I need to copy the bottom 3 rows on the ”Orders” sheet that have “No” selected in column “F” and paste the values into the “Export” sheet
Paste-Example.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can use this one-liner

Range(Sheet1.Range("F:F").Find("No"), Sheet1.Range("F" & Rows.Count).End(xlUp)).EntireRow.Resize(, 5).Copy Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1)

Author

Commented:
The code works, but it’s still coping the formulas.  I need it to paste just the values.
Top Expert 2016
Commented:
then try


 Range(Sheet1.Range("F:F").Find("No"), Sheet1.Range("F" & Rows.Count).End(xlUp)).EntireRow.Resize(, 5).Copy
 Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

Open in new window

Regards

Author

Commented:
That worked great!!!  Thank you very much.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial