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


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

Pasting a value not a formula in a macro
 

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