converting linked cells to their referenced values

[quimrider]

I have 2 spread sheets for tracking orders. The orders are entered on
the first sheet and appear in the second sheet by cell reference
formulas. Eventually when the order is no longer active, I change the
reference formula to the value of the referenced cell since it will be
deleted from the first sheet. When there are a lot of orders isn't
very practical to copy past from one spread sheet to the other. Is
there a macro or something that I can use to convert the link formula
in a cell to the value of the cell it references?

 

[glen.e.mettler]

Here is one way:
Create a column that keeps track of completed orders.
write a macro that runs from a user menu that filters for "complete"
orders,
finds the last row,
selects the appropriate range
copies that range and then pastes the same data back as values only
(pastspeacial values)
then closes the filter.

You could also set conditional formating to show the completed orders
in a different color.

Glen

 

[Ken Wright]

Put this into your personal.xls, assign a keyboard shortcut to it, eg
CTRL+SHIFT+P, then whenever you need to just select your range and hit the
key combo.

Sub PasteValues()

Application.ScreenUpdating = False

With Selection
.Copy
.PasteSpecial Paste:=xlPasteValues
End With

Application.CutCopyMode = False
Application.ScreenUpdating = False

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[quimrider]

Thanks Ken!
Works like a charm! Any suggested references or reading material tha
I could learn a little more about macro's and VB? I'm a noob when i
comes to the more advanced capabilities of excel

 

[Ken Wright]

If you're starting from scratch then personally I like John Walkenbachs
Excel Bible books. They take you from scratch through to Advanced. His
2000 Gold edition was probably one of the best value books as it was huge
and covered a ton of material. In terms of the VBA, everything in there
will be applicable now, but any of his Bible series from 2K onwards would be
a good starting point IMO.

 

[quimrider]

I got this book before your post. I must be psychic... LOL It look
good I haven't had time to read much yet. Thanks again for the help

 

[Ken Wright]

Happy reading