How To Remove VBA Code For Distribution of an Excel Sheet

  • Thread starter Thread starter G
  • Start date Start date
G

G

I am currently using Excel 2003. I have written some VBA Code in a worksheet.

I have 2 worksheet. The 1s worksheet is required by users to see and use,
and the 2nd worksheet is not required by the user, but I need it for my VBA
code and other things.

Is there a way to distribute my excel sheet such that others will not be
able to see my code or 2nd worksheet?? 1st of all, I would like the VBA
code to be not available to my users to see. If I can I would like the 2nd
worksheet not available for them to see either. The removal of the VBA code
is more important to me.

I would appreciate any help you can offer.


Thank You,

G
 
The answer is yes. Now, depending on where your VBA code resides will
depend on how you do what you want to do.

1. If the code resides in a public code module (these are the ones named
Module1,
Module2, etc. in the Project pane of the VB editor window.) then it is
pretty simple.
Set newWB = Sheets("Sheet1").Copy
ActiveWorkbooik.SaveAs Filename:="<giveitanewname> .xls"
The sheets.copy method creates a new workbook with one worksheet and
does not
copy the code from the public module.

2. If the code resides in the Worksheet code module you have two things
to consider.
a. Will the worksheet be useful to the user without the code?
b. Will removing the code from the worksheet code module affect the
data?

If 2.a is Yes and 2.b is No, then:

Set newWB = Workbooks.Add
Workbooks(1).Sheets("Sheet1").Cells.Copy neWB
newWB.Sheets(1).Range("A1")
This method copies formulas and formats as well as the data, but does
not bring over
any code from VBA. If you do not want formulas and format, then use
PasteSpecial.
 
If you have not put any code in the code module behind the first
worksheet, right click on the sheet tab, choose Move or Copy, select New
Workbook, check Create a Copy, and click Enter. The result is a
one-worksheet workbook with only the sheet you want and no code.

- Jon
 
Back
Top