VBA to copy values in column and Paste only values on separate sheet in workbook

[daltendavis]

I am in need of a Macro that allows me to copy a column of 150 lines and paste only it's values in a separate worksheet in the same Workbook. Currently I have:

Sub GroceryDispatchColumnCopy()
Sheets("Grocery Dispatch").Select
Range("E3:E150").Copy
Sheets("Sheet1").Select
Range("A1:A150").Select
ActiveSheet.Paste
End Sub

This allows me to copy and paste but the values pasted with formulas show up as blank. It can be a completely new code as well, just thought I would put it here for reference.

Thank you.

 

[njimack]

You can do this with a single line of code:

Sub GroceryDispatchColumnCpy()
Sheets("Sheet1").Range("A1:A150").Formula = Sheets("Grocery Dispatch").Range("E1:E150").Value
End Sub

BTW, I assume your original code has a typo, as both ranges need to be the same size.