Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Sort problem with events

  Asked By: Nisha    Date: Dec 27    Category: MS Office    Views: 895
  

I have 2 worksheets: "QOS" which contains 2 charts and "Pareto" which contains
the data for one of chart 2. When activating the sheet "QOS" I use the
Worsheet_Activate event to sort the data in sheet "Pareto". However I get error
1004: "The sort reference is not valid. Make sure it's within the data you want
to sort, and the first Sort ByBox isn't the same or blank."

The macro code is:
Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Application.StatusBar = "Updating Pareto Chart"
Range("A1").Select
Sheets("Pareto").Range("A2:O12").Sort Key1:=Range("B3"), _
Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

The data structure is:
row 2 = header row:
row 3 - 6 = data
row 7 - 12 = empty in this case, but may contain data
I want to sort is column B in descending order
I tried only sorting range A2:O6, to ignore the empty rows, but it still gets me
the same error message.

The exact same macro code runs when putting it into the Worksheet_Deactivate()
event in sheet "Pareto". Can anybody explain to me why this sort works here, but
does NOT when putting it in the Worsheet_Activate event on sheet "QOS"?

Share: 

 

No Answers Found. Be the First, To Post Answer.

 
Didn't find what you were looking for? Find more on Sort problem with events Or get search suggestion and latest updates.




Tagged: