PasteSpecial Values with Cell format

[GreenyMcDuff]

Hi all,

I am copying and pasting a table from one workbook into another.

The table I am copying from has formulas in that determine the background colour of the cell.

When I use "xlPasteValues" none of the cell formatting is copied accross.
When I use "xlPasteAll" the formulas all end up returning a "#N/A" value.

Is there anyway I can Paste Values whilst keeping the bg colour as determined by that formula.

Here is the code I am using if it helps (using Excel 2003)

Range(Relative, Cells(LastRow, LastCol)).Select
Selection.Copy
Workbooks("Sales_Core_Report_Template - Pivot Test.xls").Activate
Sheets("Performance Summary").Select
Range("B5").PasteSpecial xlPasteValues
Cells.Select
Cells.Columns.AutoFit
Selection.Copy
Selection.PasteSpecial xlPasteValues

Relative, LastRow, LastCol are all variables

Many Thanks

Chris

 

[VoG]

Try

Selection.PasteSpecial xlPasteValues
Selection.PasteSpecial xlPasteFormats

 

[GreenyMcDuff]

Ah, I thought it would be something simple. Thanks Peter