From the Edit menu, Instead of Paste, choose Paste Special | Values


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Ian" wrote in message
...
Hi Rob
As I have already mentioned, your Macro is now working perfectly, thank
you, but I am
just curious with one thing.

Say I enter a Part Number into Column A, the Part Description is then
returned in Column C,
the Date is returned into Column F, and the Due Date is returned into
Column G.
Now when that Part has been repaired and sent back to the customer, we cut
and paste
the Row of that Part from the "Book In" worksheet, to separate "Delivery
Note" xls files.
Each Delivery Note gets saved as a separate xls file with the Company Name
and Date,
i.e., "CompanyName060704.xls"

Everything is working fine, the "Part Number", "Part Description", "Date
In" and "Due Date"
all paste into the Delivery Note xls files, but I have noticed that when
it pastes the "Part Description",
it actually pastes the Formula, (which then refers back to the PARTNUMBERS
worksheet),
rather than pasting it as Text, (if you see what I mean).
The "Date In" and "Date Due" are pasted as text.
Is there a way to paste the "Part Description" as Text, (i.e., the text
that was looked up, rather
than pasting the actual Formula.
This may be good if we ever need to change the file name of the xls file
that contains the worksheet
"PARTNUMBERS", as the Formula pasted into the Delivery Note spreadsheet
won't be able to
find the file it is referencing, as that file will now have a different
filename.

Cheers
Ian


"Rob van Gelder" wrote in
message
...
Here's one way:

I've changed your layout a bit. Not that your approach doesn't work,
just
demonstrating a different way to do it.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target.Column = 1 Then
With Range("F" & Target.Row)
.Value = Date
.NumberFormat = "dd mmm yy"
End With

With Range("G" & Target.Row)
.Value = Date + 10
.NumberFormat = "dd mmm yy"
End With

With Range("C" & Target.Row)
.Formula = "=IF($A" & Target.Row & "="""","""",VLOOKUP($A" &
Target.Row & "," & "PARTNUMBERS!$A$2:$B$1000,2,FALSE))"
End With
End If
End Sub


This newsgroup is for all levels of expertise for excel programming from
beginners to experts.
I know a fair bit about Excel Programming, but I still learn something
new
every day - like that you can use a column letter in the column
parameter of
Cells: Cells(Target.Row, "F") (I never knew that!)

Cheers


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Ian" wrote in message
...
Thanks for all your input, unfortunately I can't get any of the Macros
to work.
I only started getting into Macros last week as I have always used
Formulas.
I suspect I'm not pasting the Macro into the right place, so would be
grateful
if you could help again.

I have the following Macro that returns the Date that each Part was
Booked In
down in Column F, and it also returns the Due Date down in Column G,
(which is 10 days later than the date the Part was Booked In), as we
work on a
10 day turn around.
So for example, when I enter a Part Number in one Row in Column A, it
returns
"04 Jul 04" in Column F, and it returns "14 Jul 04" in Column G, in
the same Row
for that Part Number.

So the Macro I have is:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count = 1 Then
If Target.Column = 1 Then
Cells(Target.Row, "F") = Date
Cells(Target.Row, "F").NumberFormat = "dd mmm yy"
End If
End If

If Target.Count = 1 Then
If Target.Column = 1 Then
Cells(Target.Row, "G") = Date + 10
Cells(Target.Row, "G").NumberFormat = "dd mmm yy"
End If
End If
End Sub

This Macro may not be perfect but it works well.
So where do I paste your Macros, sorry but I'm new to Macros.

Cecil, I'm not sure why you are using:
With Range("C2")
.AutoFill Range("C2:C1000")

I think this newsgroup is a bit too advanced for me, maybe I should be
in the newsgroup "microsoft.public.excel.newusers".

Thanks once again
Ian