VJ,

Your key isn't in the actual table when your sheet isn't active.

Key1:=Range(rng.Columns(ColNo).Address)

sets the key to a cell on the activesheet.

Change the key to something like

Key1:=rng.Cells(1,ColNo)

HTH,
Bernie
MS Excel MVP

"VJ" wrote in message
...
Hi,

I have data in sheet1 in col A, B and C and I need to sort the data when
the workbook is not active(either addIn) or from other sheet(Sheet2). The
following is the code i am using but its gives me error.

Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A25:C44")
'If sortcriteria is None then dont do anything
'If sortcriteria is Ascending then do Ascending sorting
If sortCriteria = "Ascending" Then rng.sort
Key1:=Range(rng.Columns(ColNo).Address), _
Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom: Exit
Sub

Can somebody suggest me a way out?

Thanks