Modify VBA code to copy values only

[Bremen]

Hello,
I was wondering if anyone might know how to modify the following code so that it does Not replace the destination cells formating. (i.e. background color, borders, etc...)


The following code is part of that which is used to populate a schedule based on which number is entered into a piticular cell. [12 week rotation]
-----Start code example-----

Select Case Worksheets("WeekRotations").Range("d2")
Case "1"

' Start of Sunday for week one

Worksheets("WeekRotations").Range("hs1sun").Copy _
Destination:=Worksheets("rotcopy").Range("kevinSun")

Worksheets("WeekRotations").Range("hs2sun").Copy _
Destination:=Worksheets("rotcopy").Range("mattSun")

-----End code example (code continues like that for 6 people / twelve weeks)-----
Notes: hs1sun, kevinSun, etc... all refer to Named Ranges


I know it would likely have been easier to do the whole thing with a simple array but I did not have the time to figure it out. (Haven't coded much in the last 3-4 years)

[Bremen]

Found the answer - thanks

[bhofsetz]

Have you tried a pastespecial instead of just copy to destination?

For example, replace:

Worksheets("WeekRotations").Range("hs1sun").Copy _
Destination:=Worksheets("rotcopy").Range("kevinSun")

With

Worksheets("WeekRotations").Range("hs1sun").Copy
Worksheets("rotcopy").Range("kevinSun").pastespecial Paste:=xlValues

Hope this helps.