current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a workbook that creates 3 logs. Before close I need those three sheets to copy into a new workbook then I need both workbooks to save and close themselves. I've gotten as far as the code below and now when I try to run the module it brings up the macro window and won't run. I do have this stored in ThisWorkbook. Any input would be much appreciated.

                                                                Mary

Option Explicit

Private Sub BeforeClose(Cancel As Boolean)

'Save worksheets to new workbook
    Dim strSaveName As String
    Dim DstFile As String 'destination File name
    Dim DataWorkbook As Workbook

'Unhide Worksheets
    Set DataWorkbook = ActiveWorkbook
    DataWorkbook.Sheets("Service Change Log").Visible = True
    DataWorkbook.Sheets("Transaction Log").Visible = True
    DataWorkbook.Sheets("Call Initiation Log").Visible = True

    ChDir "P:\CSR Rollback Tool\Test"
    strSaveName = Replace(Application.UserName, ",", "")


'Copy Sheets
    Sheets(Array("Service Change Log", "Transaction Log", "Call Initiation Log")).Copy

'Hide Worksheets
    Sheets("Service Change Log").Visible = False
    Sheets("Transaction Log").Visible = False
    Sheets("Call Initiation Log").Visible = False

'Close Worksheet
    MsgBox "P:\CSR Rollback Tool\" & strSaveName & Format(Now(), "yyyymmdd hh-mm") & ".xlsx"
    Application.DisplayAlerts = False
    DstFile = "P:\CSR Rollback Tool\test\" & strSaveName & Format(Now(), "yyyymmdd hh-mm") & ".xlsx"
    Workbooks(DstFile).Close SaveChanges:=True
    DataWorkbook.Close Save:=True

End Sub
share|improve this question
    
Try just closing the workbook. From your text I understand you are pressing F5 to see if the macro works. If I do this, I also get a blank Macro window with no macros. But if I close the workbook my Workbook_BeforeClose fires as expected. BTW @jacouh is right: you need to add Workbook_ in front of BeforeClose to have the sub trigger when the workbook closes. –  Karl Rookey Nov 8 '13 at 17:01
    
Or: if you want to test the code with the worksheet open, I think you can comment out the (Cancel as Boolean) and then test it using F5. –  Karl Rookey Nov 8 '13 at 17:06
    
And that was my final issue. It's firing off like a champ now. Thank you so much for the help! –  user2921708 Nov 8 '13 at 17:30
add comment

2 Answers

up vote 1 down vote

The newly created workbook will have focus after:

'Copy Sheets
    Sheets(Array("Service Change Log", "Transaction Log", "Call Initiation Log")).Copy

And so:

'Hide Worksheets
    Sheets("Service Change Log").Visible = False
    Sheets("Transaction Log").Visible = False
    Sheets("Call Initiation Log").Visible = False

will try and hide the sheets, but you can't have a workbook with no visible sheets* so you just need to rejig your code slightly and it should work.

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Save worksheets to new workbook
    Dim strSaveName As String
    Dim DstFile As String 'destination File name
    Dim DataWorkbook As Workbook

'Unhide Worksheets
    Set DataWorkbook = ActiveWorkbook
    DataWorkbook.Sheets("Service Change Log").Visible = True
    DataWorkbook.Sheets("Transaction Log").Visible = True
    DataWorkbook.Sheets("Call Initiation Log").Visible = True

    ChDir "P:\CSR Rollback Tool\Test"
    strSaveName = Replace(Application.UserName, ",", "")


'Copy Sheets
    Sheets(Array("Service Change Log", "Transaction Log", "Call Initiation Log")).Copy

'Save new workbook
    MsgBox "P:\CSR Rollback Tool\" & strSaveName & Format(Now(), "yyyymmdd hh-mm") & ".xlsx"
    Application.DisplayAlerts = False
    DstFile = "P:\CSR Rollback Tool\test\" & strSaveName & Format(Now(), "yyyymmdd hh-mm") & ".xlsx"
    activeworkbook.saveas(DstFile)
    activeworkbook.close true

'Hide Worksheets in original workbook
    DataWorkbook.Sheets("Service Change Log").Visible = False
    DataWorkbook.Sheets("Transaction Log").Visible = False
    DataWorkbook.Sheets("Call Initiation Log").Visible = False

'Close Worksheet
    DataWorkbook.Close Save:=True

End Sub
share|improve this answer
    
jaybee, got it and thank you, however, when I try to run the code the macros window pops up and my macro isn't in the list. If I save, the window doesn't pop up, but the code never fires because it never hits my breakpoint. –  user2921708 Nov 8 '13 at 16:13
    
As @Jacouh mentions stackoverflow.com/a/19862953/1372610 you'll need to use the appropriate naming convention for the beforeClose event. Edited above. –  jaybee3 Nov 8 '13 at 16:53
    
It works! It works! Thanks jaybee! –  user2921708 Nov 8 '13 at 17:31
add comment
up vote 1 down vote

First try this, change event handler name as:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Instead of:

Private Sub BeforeClose(Cancel As Boolean)
share|improve this answer
    
Thank you so much for the input; it's working now! –  user2921708 Nov 8 '13 at 17:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.