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: 1686

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"
Sheets("Pareto").Range("A2:O12").Sort Key1:=Range("B3"), _
Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

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"?



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.