MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Delete code after saving worksheet to a new workbook?

  Asked By: Ibthaj    Date: Mar 22    Category: MS Office    Views: 1377

I have a file that creates new worksheets, prompts the user to save-as
the workbook, deletes all the worksheets but one (called "Summary"),
then prompts the user a 2nd time to save just the Summary worksheet.
This allows the user to save 2 versions, a detailed version and a
summary version. Probably sounds funky but it works great.

My problem is that all the macros in the main workbook save with the
new Summary workbook. Is there a way through vba to delete all the
other code after saving the Summary?



3 Answers Found

Answer #1    Answered By: Aakifah Khan     Answered On: Mar 22

Yes, you can. The code  is accessible in a collection, and you can certainly
change it.

However, can I suggest a possibly easier way ...

- Create a new workbook
- Copy the summary  sheet to it
- Delete any other sheets in it (i.e. the default "Sheet1", "Sheet2", etc)
- Save and close this workbook  as your summary.

This is all done in just a few lines of quite straightforward code. Manipulating
the vba  code object is much less straightforward.

An alternative is to leave the code in the summary workbook, but have it check
for the absence of a key worksheet  and just not do anything if the sheet isn't

Answer #2    Answered By: Chelsea Rose     Answered On: Mar 22

You may want to think about pastespecial'ing just the values as well.
That makes it a "real" summary... sort of.

Answer #3    Answered By: Leigh Carpenter     Answered On: Mar 22

If the summary  sheet contains formulae
that reference other sheets, it is important that you do a paste values to stop
the summary sheet having links back to the non-summary sheet.

You could do a copy of the sheet first, so that the formatting is correct, then
select the entire sheet and copy/paste-values of itself to itself.

Didn't find what you were looking for? Find more on Delete code after saving worksheet to a new workbook? Or get search suggestion and latest updates.