Save a copy of particular sheet minus VBA, CommandButton of parent workbook

[steveblack2006]

Hi

Ok here is what I am trying to do.

I have a workbook (with two sheets, one being the data sheet) that
essentially prints an Invoice in triplicate and numbers them serially.
What I want to do is to save a copy of the Sheet named 'Invoice' as a
separate workbook in a sub folder, minus the macros and command button
of the parent Sheet ("Invoice"), using VBA.

SaveAs feature saves the data as well as the macros. I read Chip's
article of deleting the VBA code through VBA, though it requires the
VBA modules to be unprotected, plus involves setting all the client
computer's Excel security ssetting to modified, which I am loath to do.

Sheets("Invoice").Copy

Above code does what I need, partially, in that it copies the data
minus vba code (though it copies the activex command button as well).
However I have no clue how to do following:

1. Save that new workbook with a file name (say invoice number) in a
sub folder of current file.
2. Remove the CommandButton from the sheet.

Can above be achieved from VBA code placed in the parent Workbook?

Any help would be appreciated.

Thanks

 

[Dave Peterson]

Copying that worksheet to a new workbook will copy the code behind that
commandbutton, too.

You'll still need Chip's code to delete that code (and all the other stuff you
don't want to use).

You could replace the commandbutton with a button from the Forms toolbar. That
button will have a macro in a General module assigned to it. So you'll have a
little work moving and changing your existing macro.

But then

Option Explicit
sub testme()
dim wks as worksheet

worksheets("Invoice").copy 'to a new workbook

set wks = activesheet 'that new sheet in that new workbook

with wks
.buttons(1).delete
.parent.saveas filename:=whateveryouwanthere, fileformat:=xlworkbooknormal
.parent.close savechanges:=false
end with
end sub

(Untested, uncompiled.)

Another option would be to create a template and set it up the way you want.
Then just copy the cells and paste where you want them.

 

[steveblack2006]

Dave

Regret the delayed reply.. could check the reply only today.

This is to say thanks a lot. Your suggestion and code worked without
any tweaks, other than setting the filename.

It was simpler than removing code using vba.

Thanks again ..better say thanks late than never