Thread: Copy specific range from sheet to another if condition in cell met

Hello Guys..I am new to this forum and have seen a lot of VBA questions answered here. I would like an answer to one of my own issues.

I have attached a sample excel sheet with certain formulas for specific columns.
Following is my basic requirement : I want to copy a specific range, instead of complete row and paste it into another sheet if a condition is met in column.
In this forum I only found examples of copying a row and pasting into another sheet.

For example, in the workbook provided, "JAN-12" sheet has the data with formula in H column.
"DELIVERY" sheet has corresponding columns with last 2 columns having formula in them.

When I use the

Cell.EntireRow.Copy

it will copy the entire row and paste entrie row too as per paste code

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

But this will remove any formulas that I have set in the last 2 columns and also paste "Rejection details" columns from Jan-12 sheet to Delivery sheet.

Kindly help with a macro code that will copy only the first 10 columns to another sheets for the corresponding cell in which criteria is met.

*criteria : If "Status" column has "C" then data from first 10 columns in that row should be copied to "Delivery" sheet.

Hope this makes sense...open to any queries.

I am not worried about the paste format...

I am looking for copying specific range from one sheet to specific range on another sheet.


**also need clarification on following

ActiveSheet.Paste Destination:=DestSheet.Cells(lRow, 1)

In above code, paste operation is performed but formula is pasted and not the result, which is needed.

Try the sheet attached (created in Excel 2010, but hope it will work with 2007)
activex command buttons should transfer required data, you will need to adjust code
Regards

WOW!! ...amazing how your mind works...never thought of implementing autofilter..

mardyl :: Thanks a ton for the quick reply...saved me hours of research time (..though i wasted a lot of time on it already..)

Anyways...addition to it, can we keep the original color, font, alignment to the copied data in delivery sheet?

At present the code under yellow button copies values only, try to change it

ActiveSheet.Range("G4:G260").AutoFilter Field:=1, Criteria1:="C"
    Range("A4:J260").Select
    Selection.Copy
    Sheets("DELIVERY").Select
    ActiveSheet.Range("A2").Select
    ActiveSheet.Paste

If you found it helpful please add a point to my reputation by clicking small asterix in bottom left corner of my post

Thanks again for the quick resolution...works like a charm...


Reputation already given...