hellfire45
Active Member
- Joined
- Jun 7, 2014
- Messages
- 462
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!
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!
Code:
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