Copy/Paste Macro & Formatting Issues
 

Because of the problem with merged cells and pasting values I'd sugg4est
pasting formulas and then converting them to cvalues. Also I'd suggest
pasting the comments and then clearing them. An example:

Sub a()
Dim Cell As Range
Workbooks("Book1.xls").Activate
Range("A1:C12").Copy
Workbooks("Book2.xls").Activate
ActiveSheet.Paste Range("A1")
Range("A1").PasteSpecial 8
Selection.ClearComments
On Error GoTo ExitThis
For Each Cell In Selection.SpecialCells(xlCellTypeFormulas)
Cell.Value = Cell.Value
Next
ExitThis:
End Sub


--
Jim Rech
Excel MVP
"JimK" wrote in message
...
| Hi,
| I've written a macro that copy/pastes data as values into a new workbook
| (this eliminates formulas and proprietary informtion contained in the
source
| workbook). Is there a straightforward way for me to have the macro copy
over
| all of the graphics, data as values, column widths, formatting and merged
| cell formats BUT not copy over any comments in the cells? I can't just
copy
| over the worksheet, becuase the sorce data is a selected range from the
| source worksheet, not the whole worksheet. Right now, I'm using:
| ActiveSheet.Paste
| Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
| False, Transpose:=False
| Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
| False, Transpose:=False
| Selection.PasteSpecial 8
| Though probably archaic, this code copies graphics, values, formats and
| column widths. However, it also copies cell comments and the program
| halts/crashes when there are merged cells in the source workbook.
| Any help you can provide would be greatly appreciated. Thanks.