PasteSpecial Values and cell format

[cccbzg]

Hi,

This is a snippet of my excel macro. I can't seem to figure out how to paste both the values AND the cell formatting. I get the values I need but have had weird formatting output with all of my attempts. Any help would be much appreciated. Thanks all of you Mr. Excel heroes!!

Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Set copySheet = Worksheets("Summary Sheet")
Set pasteSheet = Worksheets("Your Dates")
Application.ScreenUpdating = False
copySheet.Range("A" & i & ":M" & i).Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues

 

[RasGhul]

Try adding next line

Selection.PasteSpecial Paste:=xlFormats

 

[cccbzg]

Thanks for this suggestion. It did get me the formats, but not from the worksheet I want the format from. How can I specify? Or connect this to my xlPasteValues command?

Many thanks,
Bonnie

 

[Fluff]

how about

With pasteSheet.Cells(Rows.count, 1).End(xlUp).Offset(1)
   .PasteSpecial xlPasteValues
   .PasteSpecial xlFormats
End With

 

[cccbzg]

Woo Hoo! That worked!

Thanks SO much!

 

[Fluff]

Glad to help & thanks for the feedback