Copy / Pastwe a sheet but lose its code 1

[Welshbird]

I have a sheet within a workbook that I am 'copying' to a new worksbook and emailing.

At the moment I copy like this:

        With Destwb.Sheets(1).UsedRange
            .Cells.Copy
            .Cells.PasteSpecial xlPasteFormulas
            .Cells(1).Select
        End With
        Application.CutCopyMode = False

But, what I really want to be able to do is leave the formulas in there (to be visible to the recipient) but I really want not to send the code behind the sheet.

(The code is copying some data around teh cells and applying some conditional formatting to specific ranges so is in as a worksheet_change event and does work really well, but when this is emailed I no longer want that change functionality to work.

Any ideas chaps?

Ta.

Fee

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

Hi,

Copy the SHEET rather than the CELLS in the sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Welshbird]

Ah... fabby - thanks Skip - Off to try right now.



Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

I tried this:

        With Destwb.Sheets(1)
            .Copy
            .Paste
            .Select
        End With
        Application.CutCopyMode = False

But the code behind the sheet is still there.

Am I just misunderstanding? (The code is actually behind the sheet rather than anywhere else you see)

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

But the code behind the sheet is still there.

Is that not what you wanted?

If not, I am confused!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

That's what he said in OP:

I really want not to send the code behind the sheet.

Have fun.

---- Andy

 

[SkipVought]

SourceWorksheet.cells.copy

DestinationWorksheet.[A1].pastespecial xlPasteFormulas

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Welshbird]

So then, the bit earlier in my code where I do this:

    Sheets("Calculations").Copy
    Set Destwb = ActiveWorkbook

- That'd be wrong then!

So, I need to open a new workbook and then copy the cells and pastespecial as xlformulas?

Thanks chaps.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

I advise against using ActiveWhatever in most instances. When dealing with multiple workbooks, you must be careful to reference your objects UNAMBIGUOUSLY, like

    WkBook1.Sheets("Calculations").Cells.Copy
    WkBook2.Sheets([i]Whatever[/i]).[A1].PasteSpecial xlpasteformulas

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Welshbird]

Well, what I have done now (which may well not be the most eligant solution, but its fast enough and it works) is this:

COpy worksheet to new workbook.

Copy cells.
New Sheet:
Paste special format.
Paste special formulas.
Delete old sheet.

so, now I have the cells with the data and the formulas (with the nice pretty formatting. Grr), but no code and no buttons.

Happy!

Skip - Thanks again. Have a twinkly thing..



Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen