Programmaticly removing macros

[groston]

This should be simple :-(

I have a spreadsheet application. In a nutshell, it has two sheets - "InputSheet" onto which the user enters values and presses buttons and "OutputSheet" which has the data nicely formatted. "OutputSheet" has some code associated with its "activate" action - this is necessary and cannot be removed.

Here is the problem: I have a button on "InputSheet" that is supposed to create a new workbook. This workbook should have only one sheet and it should be a copy of "OutputSheet". There are a number of ways to skin this cat, but all seem to resolve down to one of the two following problems:
- If I use a worksheet copy approach, the "activate" macros are copied into the new workbook and this is undesired. Also, the values in the cells contains formuls, not static values, which is again bad. I did write a macro that removed all lines of code from the workbook, however, when the workbook was next opened, it behaved as if it had macros.
- If I use a range copy approach, I lose all cell formatting, column widths, etc. I suspect that this is the better apporach, but I do not know how to copy this formatting information.

I look forward to your suggestions.

Gerry Roston
gerry@pairofdocs.net

 

[sfvb]

This should work:

Sub CopyOutputSheetToNewWorkbook()
   Dim wkCnt    As Integer
   
   Application.DisplayAlerts = False
   
   wkCnt = Workbooks.Count
   Workbooks.Add
   With Workbooks(wkCnt + 1)
      While .Sheets.Count > 1
         .Sheets(1).Delete
      Wend
      ThisWorkbook.Sheets("OutputSheet").Cells.Copy
      .Sheets(1).Cells.PasteSpecial Paste:=xlValues
      .Sheets(1).Cells.PasteSpecial Paste:=xlFormats
   End With
   
   Application.CutCopyMode = False
   Application.DisplayAlerts = False
End Sub

 

[sfvb]

Oops the second to last line should be:

   Application.DisplayAlerts = True

 

[groston]

The 'ole .Cells.PasteSpecial trick - I should have known!

Thanks!