Copy Sheet problem
 

Hi,
I have a routine that copies a worksheet, deletes all the buttons and
converts formulas to values. This all works fine.
The problem is I have a named range "CWR_Description" that is composed
of a group of merged cells. The range is used for text entry only, but
can be several sentences long and if it's too long, only part of the
text gets copied to the new sheet.
I know when I manually copy a sheet this takes place and Excel warns
you and prompts you to copy and paste the cell or cells individually.
I'm hoping someone can show me some method for handling this using
VBA,

Here's my Code

Private Sub cmdCopySaveAsCWR_Click()
Dim c As Range
Dim d As Range
ActiveSheet.Copy
Application.Dialogs(xlDialogSaveAs).Show
Application.ScreenUpdating = False
Application.EnableEvents = False
With ActiveSheet
..Unprotect ("geekk")
..Shapes("Group 11").Delete
..Shapes("Group 7").Delete
..Shapes("Group 3").Delete
Set d =
ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
For Each c In d
With c
..Value = .Value
End With
Next c
..Protect ("geekk")
End With
ActiveWorkbook.Save
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=503090