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