Pasting a value not a formula in a macro

Hi,

I have a macro that copies data from one sheet to another, the active cells in the first spreadsheet are "overwritten" each time by the user (who then uses them for another action) and the macro keeps a record of these entries by copying them into another sheet that purely acts as a "list". The list sheet uses a "next blank row" methodology and functions well.

My problem is that when the macro copies the data it appears to copy the formula relating to the date inputted by the user and not the value. This results in all records showing whatever date is CURRENTLY showing in the first sheet.....I want it to copy the value and then when the next user overwrites the first sheet, it doesnt change the second sheet.

My code is

Please Login or Register  to view this content.

Thanks

Use the macro equivalent of Edit/Paste Special/Values:

Selection.PasteSpecial xlPasteValues



Alec H wrote:
> Hi,
>
> I have a macro that copies data from one sheet to another, the active
> cells in the first spreadsheet are "overwritten" each time by the user
> (who then uses them for another action) and the macro keeps a record of
> these entries by copying them into another sheet that purely acts as a
> "list". The list sheet uses a "next blank row" methodology and
> functions well.
>
> My problem is that when the macro copies the data it appears to copy
> the formula relating to the date inputted by the user and not the
> value. This results in all records showing whatever date is CURRENTLY
> showing in the first sheet.....I want it to copy the value and then
> when the next user overwrites the first sheet, it doesnt change the
> second sheet.
>
> My code is
>
>
> Code:
> --------------------
>
> ' Save filtered customers ids to diary
>
> Sheets("Formulas").Select
> Range("P5:P60").Select
> Selection.Copy
> Sheets("Stage 1 - Visit diary").Select
> Range("A65536").End(xlUp).Offset(1, 0).Select
> ActiveSheet.Paste
>
> ' Save visit date to diary
>
> Sheets("Formulas").Select
> Range("S5:S60").Select
> Selection.Copy
> Sheets("Stage 1 - Visit diary").Select
> Range("K65536").End(xlUp).Offset(1, 0).Select
> ActiveSheet.Paste
>
> --------------------
>
>
> Thanks
>
>
> --
> Alec H
> ------------------------------------------------------------------------
> Alec H's Profile: http://www.excelforum.com/member.php...o&userid=31042
> View this thread: http://www.excelforum.com/showthread...hreadid=515686