VBA to insert cell format into cell based on cell value

Hi-

I have a worksheet that when I copy and paste (even paste special) cells from one workbook to another it does not pull the formula value over it only copies the formatting over.

I would like to copy and paste everything like this (First cell 25P120RP180355F / Second cell 355' 25P C5 male Amphenol cables 120 - 180 hoods) to whatever row is selected.

Note: Sheet 1 and Sheet 2 will not be in the same workbook

I have attached my sample worksheet. Any help is greatly appreciated