Workbook_BeforeSave Not executing commands

[deevooneh]

Please help. I'm fairly new to VBA. and I cant seem to figure out why my code isn't working. If I save manually, the Workbook_beforesave macro executes just fine. However If my macro calls to save, the beforesave macro doesn't execute any of the code at all! I step into and it just acts like its doing something, but when you view it in excel no action is taking place.

Here is my code that calls the save function, Please note this macro is in a different excel file, I have tried putting a revised version in my personal.xls and in the master file and still didn't work:

Sub CreateTemplates()

Dim TemplateFileName As String
Dim RevisionFileName As String
Dim AccountListFileName As String
Dim AccountNumber As String
Dim NumberOfAccounts As Integer
Dim x As Integer



AccountListFileName = ActiveWorkbook.Name

Range("b1").Formula = "=COUNTIF(A:A,""<>"")"
NumberOfAccounts = Range("b1").Value


For x = 2 To NumberOfAccounts

    Workbooks.Open "\\jfcfile\tlappe\Financial Planning & Accounting\2011 Budget\2011 Master Budget Template.xlsm"

    TemplateFileName = ActiveWorkbook.Name


    Workbooks(AccountListFileName).Activate

    Cells(x, 1).Select
    AccountNumber = ActiveCell.Value
     
    ActiveCell.Copy
    
    Workbooks(TemplateFileName).Activate
    
    Range("a6").Select
    
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Call Calc
    
    
    Call PasteVal
    
    Sheets("Dep. HC").Select
    Range("B1").Select
    ActiveCell.Formula = "=CONCATENATE(LEFT(Budget!E5,3),""-"",MID(Budget!E5,4,2),""-"",MID(Budget!E5,6,2),""-"",MID(Budget!E5,8,3),""-"",MID(Budget!E5,11,4))"
    Range("B35").Select
    
    Sheets("Budget").Select
    
    
    RevisionFileName = Range("A7").Value
    ActiveWorkbook.SaveAs Filename:=RevisionFileName
    
    MsgBox "The File " & RevisionFileName & " Has Been Created"
    
    Workbooks(RevisionFileName).Close savechanges:=False
    


Next x

End Sub

and here is my workbook beforesave code. (Please note this code works when saving using the excel icon, but doesnt work when executed from another macro)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Sheets("Budget").Select
Call Unpro

     'Turn off events to prevent unwanted loops
    Application.EnableEvents = False
'    Sheets("PinNumbers").Visible = xlVeryHidden
'    Sheets("ReadData").Visible = xlVeryHidden
   

     
     'Call customized save routine and set workbook's saved property to true
     '(To cancel regular saving)
    Call CustomSave(SaveAsUI)
    Cancel = True
     
     'Turn events back on an set saved property to true
    Application.EnableEvents = True
    ThisWorkbook.Saved = True
    
Call Pro

End Sub

Private Sub CustomSave(Optional SaveAs As Boolean)

Call Unpro

    Dim ws As Worksheet, aWs As Worksheet, newFname As String
     'Turn off screen flashing
    Application.ScreenUpdating = False
     
     'Record active worksheet
    Set aWs = ActiveSheet
    Dim Manager As String
    
    
    Manager = Range("b8")
    
     
     'Hide all sheets
    Call HideAllSheets
     
     'Save workbook directly or prompt for saveas filename
    If SaveAs = True Then
        newFname = Application.GetSaveAsFilename( _
        fileFilter:="Excel Files (*.xlsm), *.xlsm")
        If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
    Else
        ThisWorkbook.Save
    End If
     
     'Restore file to where user was
    Call ShowAllSheets
    Call Unpro
'    Sheets("PinNumbers").Visible = xlVeryHidden
'    Sheets("ReadData").Visible = xlVeryHidden
    aWs.Activate

     
     'Restore screen updates
     Select Case Manager
     
                Case "Admin"
            
                Range("B10").Select
                Sheets("PinNumbers").Visible = True
                Sheets("HC Raw Data").Visible = True
                Sheets("ReadData").Visible = True
                Sheets("Comp Fcst").Visible = True
                Sheets("Comp Bgt").Visible = True
                Sheets("Actual&Budget").Visible = True
                Sheets("Historical Raw Data").Visible = True
                Sheets("Macros").Visible = True
                
                Call Unpro
                
                Case Else
                Sheets("PinNumbers").Visible = xlVeryHidden
                Sheets("ReadData").Visible = xlVeryHidden
                Sheets("HC Raw Data").Visible = xlVeryHidden
                Sheets("Comp Fcst").Visible = xlVeryHidden
                Sheets("Comp Bgt").Visible = xlVeryHidden
                Sheets("Actual&Budget").Visible = xlVeryHidden
                Sheets("Historical Raw Data").Visible = xlVeryHidden
                Sheets("Macros").Visible = xlVeryHidden

                'Call HideAllSheets
                
                Call Pro

          End Select
     
     
    Application.ScreenUpdating = True
    
Call Pro
End Sub

Any help will be greatly appreciated!!!!

 

[deevooneh]

Re: macro help

anyone?!?!