dgt
07-20-2009, 07:52 AM
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.
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.