Macro Strategy - "Peeling" Off Sheets

[Chuckles123]

I have a workbook with 5 sheets and VBA Code attached. The sheets are
updated each business day and carry a rolling 6 months of history. I
want to peel off 2 of these sheets and place them into a new workbook,
with a date stamp in the filename, with zero VBA Code; this new
workbook will be attached to a daily e-mail.

My code has been peeling off 1 sheet, WITH ZERO CODE, into a new
workbook and e-mailing such workbook for several months. However, 2
sheets inserted into a new workbook is more difficult, at least for
me.

A related query: in my existing code, after bringing in new data to
the 6-month history workbook, I was playing around with 1) saving the
parent workbook; 2) deleting 3 of the sheets; 3) saving the active wb
(of course, this includes VBA Code); 4) activating the parent wb; and
5) e-mailing the
2-sheet wb.

Two problems: a) the e-mailed wb would include VBA Code (not
desirable); and b) the e-mail fails because the error message indicates
that the file to be attached is in use by another process. When
stepping thru the Code, when the above step 4) is processed, nothing
appears to happen. (I thought that when a File SaveAs occurs, that the
original file was still hanging around in the "background" -- but I'm
not sure that is the case. Also, I guess if two workbooks share the
same VBA Code, it is problematic to attach one of them to an e-mail.)

Strategic suggestions would be appreciated,
Chuckles123

 

[Dave Peterson]

Chip Pearson has code to remove code at:
http://cpearson.com/excel/vbe.htm

But I've found it sometimes simpler to just copy the cells and paste that to a
new worksheet. (You'd have to do it for each sheet.)

(maybe even paste special|values???)

Ron de Bruin has tons of code for emailing via excel:
http://www.rondebruin.nl/sendmail.htm

You may want to review how he does it.

 

[Chuckles123]

Thanks Dave for your post.

Chip's site is excellent and Ron's is as well. I used Ron's site i
drafting my e-mail code. I searched Chip's site re: this query and di
not find anything.

I really do not want to delete anything; my 5-sheet workbook is ongoin
and an historical record. However, on a daily basis, I want to "peel
off two of the sheets into a newly created workbook. I have bee
"peeling" one sheet into one wb without difficulty; however, my quer
relates to 2 sheets into one wb.

Chuckles12

 

[Chuckles123]

Thanks very much, Dave -- I think this works.

Option Explicit
Sub testme()

Dim wkbk As Workbook
Dim newwkbk As Workbook

Set wkbk = Workbooks("book1.xls")

Set newwkbk = Workbooks.Add

wkbk.Worksheets(Array("sheet1", "sheet3")).Copy _
before:=newwkbk.Worksheets(1)

I added (at the end of your code):

ActiveWorkbook.SaveAs Filename:=myPath & _
"PAS CAP ITEM__" & ActiveSheet.Name & " " & Format (PX_Date, _
"mmm_dd_yy") & ".xlS", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

fName = ActiveWorkbook.Name

ActiveWorkbook.Close

fName = myPath & fName

Call EMAIL_CODE

... more stuff ...

End Sub

Does this look OK?

Thanks again for a response,
Chuckles123

 

[Chuckles123]

I added 'newwkbk.Activate' before the SaveAs stmt.
OK?
Chuckles12

 

[Dave Peterson]

It looks fine to me--but does it work ok <vbg>?

Thanks very much, Dave -- I think this works.

Option Explicit
Sub testme()

Dim wkbk As Workbook
Dim newwkbk As Workbook

Set wkbk = Workbooks("book1.xls")

Set newwkbk = Workbooks.Add

wkbk.Worksheets(Array("sheet1", "sheet3")).Copy _
before:=newwkbk.Worksheets(1)

I added (at the end of your code):

ActiveWorkbook.SaveAs Filename:=myPath & _
"PAS CAP ITEM__" & ActiveSheet.Name & " " & Format (PX_Date, _
"mmm_dd_yy") & ".xlS", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

fName = ActiveWorkbook.Name

ActiveWorkbook.Close

fName = myPath & fName

Call EMAIL_CODE

.. more stuff ...

End Sub

Does this look OK?

Thanks again for a response,
Chuckles123

 

[Dave Peterson]

Shouldn't need it.

When you copy the sheets to the new workbook, the new workbook should become the
active workbook.

(But it won't hurt.)

 

[Chuckles123]

Dave,

I, too, think it will run OK, but I will let you know either way.
Thanks again,

Chuckles12

 

[Ron de Bruin]

Hi Chuckles123

If you use my Mail add-in you can send the sheets you want without code with a few clicks

 

[Chuckles123]

There was a slight nuisance with 3 extra blank sheets, but I was able t
dispatch them with 'Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Delete
with out too much difficulty.

Chuckles123

P.S.: Ron, Thanks for your offer of your Mail add-in, but I am all se
in that area