macro or formula to paste values from one tab to another

[Mel]

I have a spreadsheet with a number of tabs. From one tab (severance)
I want to copy values in cells A9 to F38 and paste into tab LSAC cells
A9 to F38. I have so far written but need it to do this
automatically. Also,
I have conditional formatting that I want to retain. Not sure how to
do both (past values and retain conditional format on cells I am
pasting to. (cells E9 to 38 have an indicator, if it is greater than
1, changes font color on row to red).

Sub paste_valueClick()
'
' paste_valueClick Macro
' Macro recorded 05-01-2011 by pilgrimm
'
Sheets("SEVERANCE").Select
Range("A9:F38").Select
Selection.Copy
Sheets("Long Service Awards Calc (2)").Select
Range("A9").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A9").Select
End Sub

thx
Mel

 

[Don Guillett Excel MVP]

I have a spreadsheet with a number of tabs.  From one tab (severance)
I want to copy values in cells A9 to F38 and paste into tab LSAC cells
A9 to F38.   I have so far written but need it to do this
automatically.  Also,
I have conditional formatting that I want to retain.  Not sure how to
do both (past values and retain conditional format on cells I am
pasting to.  (cells E9 to 38 have an indicator, if it is greater than
1, changes font color on row to red).

Sub paste_valueClick()
'
' paste_valueClick Macro
' Macro recorded 05-01-2011 by pilgrimm
'
    Sheets("SEVERANCE").Select
    Range("A9:F38").Select
    Selection.Copy
    Sheets("Long Service Awards Calc (2)").Select
    Range("A9").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("A9").Select
End Sub

thx
Mel

A couple of questions:
I want to do this automatically means?
I want CF to stay. Explain fully.
"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."