Excel formats do not copy

Question

In copying and pasting cell formatting in VBA, the beginning format applied in the "from" cell is being applied to the entire "destination" cell where the formatting is pasted.

I have a cell that contains a mixed format.  Only the beginning format is being picked up and pasted into the new cell.
In addition, borders do not copy at all.

Is this a known bug?
(Excel 2007)


Worksheets(SetupSheet).Range("Legend").Copy
With Sheets(ExecSheet).Range("$A$6") 
        .PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True
        .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True
End With

Answer 1

Try this

With Sheets(ExecSheet).Range("$A$6")
     .PasteSpecial _
        Paste:=xlValues, _
        Operation:=xlNone, _
        SkipBlanks:=False, _
        Transpose:=False
     .PasteSpecial _
        Paste:=xlFormats, _
        Operation:=xlNone, _
        SkipBlanks:=False, _
        Transpose:=False
     .PasteSpecial _
        Paste:=xlAll, _
        Operation:=xlNone, _
        SkipBlanks:=False, _
        Transpose:=True
End With

---

jdweng

Answer 2

Thanks Joel,

The xlAll paste method always returns 

    Error in BuildExecutiveSummary: PasteSpecial method of Range class failed

Tried xlPasteAll as well.  Returned same error

 Based upon another post I read, I had a line in my code
 Application.CutCopyMode = False 
This line occurred several lines AFTER the paste method call.  Because the line was in the code, it threw the error.  The error was thrown on the Paste method and never hit the CutCopyMode=False line.  The error was thrown anyway.  MS should research this.

Borders do not copy with this.  I will need to apply the borders manually. 

Thanks for your help.

Answer 3

Turns out the xlAll always returns a failed error.  Tried xlColumnWidths which also fails every time.

Answer 4

The code below should work.  My orignal code should of used xlPasteAll.  If this code doesn't work I suspect the is conditional formating in the original data.  I thought copying the values and formating would eliminate properties that would not transpose (like merge cells and formulas) that may cause problems.  But formats will copy conditional formating.  So check for conditional formating.

Worksheets(SetupSheet).Range("Legend").Copy
With Sheets(ExecSheet).Range("$A$6")
     .PasteSpecial _
        Paste:=xlValues, _
        Operation:=xlNone, _
        SkipBlanks:=False, _
        Transpose:=False
     .PasteSpecial _
        Paste:=xlFormats, _
        Operation:=xlNone, _
        SkipBlanks:=False, _
        Transpose:=False
     .PasteSpecial _
        Paste:=xlPasteAll, _
        Operation:=xlNone, _
        SkipBlanks:=False, _
        Transpose:=True
End With

---

jdweng