VBA Won't calculate. Need desperate assistance.

[hellfire45]

Pretty desperate here. I've wasted a lot of time.

I have a very complex worksheet that changes the name of a customer in a field "E1". This cell is tied to every formula in the worksheet. So I have a loop that changes the name in E1 and then calculates and generates an exported worksheet. It's supposed to do this about 20 iterations.

The code doesn't error and it exports and saves the worksheets but they don't calculate before exporting. They should all have very different numbers but they all come out the same. If I stop the code and go into the worksheet and hit SHIFT + F9 the sheet calculates.

Based on the code below, in which the line "Application.Calculate" is supposed to calculate the file, is there a way for me to guarantee that this worksheet (VBNamed "COM") will calculate? What can I do to make sure this thing doesn't jump the application.calculate line without calculating?

Thank you so much!

For xlist = Application.Match("Customer Name", ref.Columns(1), 0) + 1 To lastreport            
Application.StatusBar = "Phase 8: Generating Customer Report " & xlist - 2 & " of " & lastreport - 2 & " Reports Complete..."
            If COM.FilterMode = True Then COM.ShowAllData
            
            'resets all filtering in all of the meaningful worksheets.  This incldues the main report sheet which might undergo some truncating via hiding rows lateron.
            'resets the manufacturer store to the next in the rotation of vendors
            'sets the customer name in the header which ties to most formulae on the worksheet
            customername = ref.Cells(xlist, reportcolumn).value
            z = Application.Match("*", COM.Rows("1:1"), 0)
            COM.Cells(1, z).value = customername
            'ActiveSheet.Calculate
        [B]    Application.Calculate
                  [/B]
            'creates individual copies of customer commit reports
            Set WBNEW = Workbooks.Add
            lastr = COM.Columns(1).Find("*", , xlValues, , xlRows, xlPrevious).Row
            voidsku = Application.Match("voidsku", COM.Rows(commit_first_row), 0)
            COM.Range("$A$2:$BS$" & lastr).AutoFilter Field:=Application.Match("voidsku", COM.Rows(commit_first_row), 0), Criteria1:="0", Operator:=xlFilterValues

 

[shg]

Do you have calculation set to Manual for some reason?

Do you have EnableCalculation false for some worksheets?

 

[hellfire45]

Do you have calculation set to Manual for some reason?

Do you have EnableCalculation false for some worksheets?

I don't have enable calculation set to false but I do have the automation set to Manual calculation because I can't have this thing calculating each time something happens. So I tell it to calculate when it needs to.

I am doing the following:

Application.DisplayAlerts = FalseApplication.CalculateBeforeSave = False
Application.Calculation = xlManual
Application.ScreenUpdating = False
Application.EnableEvents = False

Any other ideas about how to force a calculation guaranteed?

 

[avicric]

Hey hellfire..... have you tried to refresh the data(sheet ;workbook) before it calculates.....

also can you try the below...

Application.DisplayAlerts = FalseApplication.CalculateBeforeSave = False
Application.Calculation = xlManual
Application.ScreenUpdating = False
Application.EnableEvents = True

 

[hellfire45]

Hey hellfire..... have you tried to refresh the data(sheet ;workbook) before it calculates.....

also can you try the below...

Application.DisplayAlerts = FalseApplication.CalculateBeforeSave = False
Application.Calculation = xlManual
Application.ScreenUpdating = False
Application.EnableEvents = True

Application.EnableEvents = True was a good suggestion but this didn't fix it. Is it possible the worksheet is corrupt or something?

 

[avicric]

Hi hellfire....

about the sheet cant say....but....if you feel that would suggest you to export all the modules from VBA and then save the sheet as .xls close the file, reopen it from the new saved path and import the modules and save the book as a macro enabled or binary and try it...

aso would request you to a add a line to your code which will refresh the entire sheet(workbook) and then try to calculate before creating/exporting data to new sheets.