+ Reply to Thread
Results 1 to 10 of 10

Macro Strategy - "Peeling" Off Sheets

  1. #1
    Forum Contributor
    Join Date
    10-03-2004
    Posts
    102

    Question Macro Strategy - "Peeling" Off Sheets

    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

  2. #2
    Dave Peterson
    Guest

    Re: Macro Strategy - "Peeling" Off Sheets

    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 wrote:
    >
    > 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
    >
    > --
    > Chuckles123
    > ------------------------------------------------------------------------
    > Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948
    > View this thread: http://www.excelforum.com/showthread...hreadid=570973


    --

    Dave Peterson

  3. #3
    Forum Contributor
    Join Date
    10-03-2004
    Posts
    102

    Thanks, Dave -- I Have More Info

    Thanks Dave for your post.

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

    I really do not want to delete anything; my 5-sheet workbook is ongoing 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 been "peeling" one sheet into one wb without difficulty; however, my query relates to 2 sheets into one wb.

    Chuckles123

  4. #4
    Forum Contributor
    Join Date
    10-03-2004
    Posts
    102

    Lightbulb Thanks, Dave -- I Saw Your Post On The Other Thread

    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

  5. #5
    Forum Contributor
    Join Date
    10-03-2004
    Posts
    102

    One More Change

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

  6. #6
    Dave Peterson
    Guest

    Re: Macro Strategy - "Peeling" Off Sheets

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

    Chuckles123 wrote:
    >
    > 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
    > ------------------------------------------------------------------------
    > Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948
    > View this thread: http://www.excelforum.com/showthread...hreadid=570973


    --

    Dave Peterson

  7. #7
    Dave Peterson
    Guest

    Re: Macro Strategy - "Peeling" Off Sheets

    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 wrote:
    >
    > I added 'newwkbk.Activate' before the SaveAs stmt.
    > OK?
    > Chuckles123
    >
    > --
    > Chuckles123
    > ------------------------------------------------------------------------
    > Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948
    > View this thread: http://www.excelforum.com/showthread...hreadid=570973


    --

    Dave Peterson

  8. #8
    Forum Contributor
    Join Date
    10-03-2004
    Posts
    102

    Will Not Be Able To Test Until Later Today

    Dave,

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

    Chuckles123

  9. #9
    Ron de Bruin
    Guest

    Re: Macro Strategy - "Peeling" Off Sheets

    Hi Chuckles123

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


    --
    Regards Ron de Bruin
    http://www.rondebruin.nl



    "Chuckles123" <Chuckles123.2ceu98_1155357911.3675@excelforum-nospam.com> wrote in message
    news:Chuckles123.2ceu98_1155357911.3675@excelforum-nospam.com...
    >
    > 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
    >
    >
    > --
    > Chuckles123
    > ------------------------------------------------------------------------
    > Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948
    > View this thread: http://www.excelforum.com/showthread...hreadid=570973
    >




  10. #10
    Forum Contributor
    Join Date
    10-03-2004
    Posts
    102

    Wink Dave, Thanks Again -- It Worked

    There was a slight nuisance with 3 extra blank sheets, but I was able to 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 set in that area.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1