Saving subset of Worksheets

**rtiltins** · 11-04-2008, 12:58 PM

Hi all,
I am trying to e-mail a portion of a spreadsheet that I have. My approach is the following: I save a copy in a temporary folder, open that copy, remove all the sheets that I don't need to send out, save it (same name as the e-mail subject), and then e-mail as an attachment. The current approach does not remove the macros and references though. I use the following to remove the macros and references:

Set vbCom = Application.VBE.VBProjects(EmailSubject).VBComponents
vbCom.Remove VBComponent:=vbCom.Item("AddIn_Manager")
    
Set vbRef = Application.VBE.VBProjects(EmailSubject).References
vbRef.Remove Reference:=vbRef.Item(1)

Any thoughts on this? or perhaps suggestions for another method?

**shg** · 11-04-2008, 02:38 PM

Why not just copy the sheets you need to a new workbook?

**rtiltins** · 11-04-2008, 03:15 PM

This way (saving copy of workbook) seemed easier because there are very many pages to copy, with a lot of images/charts that needed to be copied as well. Sometimes there are more than 20 worksheets for which all cells, all charts and images/logos would also need to be copied. Using the savecopyas approach I just needed to remove three worksheets that I don't want to be sending out.

Finally found a pretty helpful link related to this, and was able to get the desired result using code from there. Here is the link that I used:

http://www.cpearson.com/excel/vbe.aspx

Thanks for the comments.