I am looking for some help copying some specific cells from one worksheet to
the last row on another sheet. Basically I have various which I have named.
I want to a macro that will take these cells and paste them to a new row.
For example on Sheet one I may have the following Named cells

C2 = Name
D3 = Date
C4 = Company
E6 = State

I then want a macro to past these in the last available row, sequentially in
each column starting in column A. For example, assuming the last available
row is 20, the Sheet1.C2 would copy to Sheet2A20, Sheet1.D3 to Sheet2.B20,
Sheet1.C4 TO Sheet2.C20 and sheet1.E6 to Sheet2.D20.

Any help would be greatly appreciated.