Worksheets.Calculate not updating worksheet!

[dgt]

Hi all

I hope that someone can provide a solution to my problem.

I have been using the following code successfully and have not needed to update related worksheets until the workbook was saved in full.


Sub SortColumns()
With Sheets("Chqs").Columns("A:E")
.Sort Key1:=.Cells(2, 2), Order1:=xlAscending, _
Key2:=.Cells(2, 5), Order2:=xlDescending, _
Key3:=.Cells(2, 3), Order3:=xlAscending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
End With
End Sub


As the workbook is quite intensive, it can take quite a while for the save to be completed. So I tried to modify the code below by adding these 2 lines:

Worksheets("Chq Form").Calculate
Worksheets("Summary").Calculate

So that I ended up with:


Sub SortColumns()
With Sheets("Chqs").Columns("A:E")
.Sort Key1:=.Cells(2, 2), Order1:=xlAscending, _
Key2:=.Cells(2, 5), Order2:=xlDescending, _
Key3:=.Cells(2, 3), Order3:=xlAscending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
End With
Worksheets("Chq Form").Calculate
Worksheets("Summary").Calculate
End Sub


The "Chq Form" worksheet is totally derived from the worksheet that contains this code, so no manual entry is required in the "Chq Form" worksheet.

The problem is that after running this code, the "Chq Form" worksheet does not update itself with new entries. The only change that takes place is where an existing value may have been changed.

I sourced the Worksheets.Calculate from one of the books that I have but could not find anything else appertaining to this situation.

Any ideas on where I have gone wrong.

TIA ...DGT

PS: My knowledge of VBA is zilch but I try to find things out before I post a question.

[Simon Lloyd]

I seem to be quoting Charles Williams a lot today :) take a look here http://www.decisionmodels.com/calcsecretsh.htm

[dgt]

I checked out the decisionmodels.com website as Simon suggested and come to the conclusion that I can only use "Application.calculate" to update those 2 worksheets in this situation but as that applies to the whole workbook, it defeats the simplification that I was trying to achieve.

Any other thoughts on how to just update the calculations in those 2 specific worksheets or is it just not possible?

DGT

[mumin_abdul]

Try looking into DoEvents. Maybe it can help.

[dgt]

Thanks for the tip but I could'nt make use of the DoEvents as it did'nt seem to resolve the problem in the way that I want. However, as my knowledge of VBA is zilch; it may be that I could not figure out how best to incorporate this in my piece of code.

From the articles I have read, Worksheets().Calculate still appears to be the correct code for my requirements but it does not make the 'Chq Form' worksheet update itself; although the formula in the 'Summary' worksheet is being updated.

The only way that I have managed to resolve this in a fashion is using the following code:


Sub SortColumns()
With Sheets("Chqs").Columns("A:E")
.Sort Key1:=.Cells(2, 2), Order1:=xlAscending, _
Key2:=.Cells(2, 5), Order2:=xlDescending, _
Key3:=.Cells(2, 3), Order3:=xlAscending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
End With
Sheet100.Select
ActiveSheet.Calculate
Worksheets("Summary").Calculate
End Sub


Any comments/suggestions as to how to improve this code would be most welcome.

TIA ...DGT

[Aussiebear]

I think this is yet another of the threads where a sample workbook may need to be posted for a resolution to the issue to be reached.

[dgt]

I will try to make up an example workbook with just the relevent worksheets over the next few days.

DGT

[Aussiebear]

Please make sure all the relevant code is provided when attaching the sample workbook.

[dgt]

Hi all

Thanks for your continued assistance but I think that I have solved the problem as per the code below:


Sub SortColumns()
With Sheets("Chqs").Columns("A:E")
.Sort Key1:=.Cells(2, 2), Order1:=xlAscending, _
Key2:=.Cells(2, 5), Order2:=xlDescending, _
Key3:=.Cells(2, 3), Order3:=xlAscending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
End With
ActiveSheet.Calculate
Worksheets("Chq Form").Calculate
Worksheets("Summary").Calculate
End Sub


Although the "SortColumns" code appeared to update the worksheet visibly, I guess that it needed the extra "ActiveSheet.Calculate" to force the calculation into effect and make the two "Worksheets.Calculate" function correctly.

Would appreciate any last thoughts or comments as to whether this needs improving or changing in any way.

Thanks ...DGT