Copy Sheet problem

C

Casey

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top