copy all rows based on a cell value

[ajaf123]

Hello. I have a sheet with certain used rows with gaps in between. I want a macro that goes through column L and if it finds a cell that is used copy that entire row and paste it on a sheet named Compiled data. I started with a macro but it only copies the first unused row. I want the macro to copy and paste all rows where Column L is not blank. Kindly help.

 

[Rick Rothstein]

If the values in Column L are constants (that is, not formulas), then this single (albeit longish) line of code should also do what you want...

Sub MoveDataWithQuantityFromCalculationToCustomerSheet()
  Range("L2", Cells(Rows.Count, "L").End(xlUp)).SpecialCells(xlConstants). _
        EntireRow.Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp)
End Sub

 

[vcoolio]

Hello Ajaf,

Excellent! Glad I could help.

BTW, don't discard your little code!

Cheerio,
vcoolio.

 

[vcoolio]

Hello Rick,

Nice option! Ajaf should be most impressed.

Cheerio,
vcoolio.