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

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

If sortCriteria = "Ascending" Then rng.sort
Key1:=rng.Columns(ColNo).Cells(1,1), _
Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom: Exit Sub

--
Regards,
Tom Ogilvy


"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

Thanks Bernie and Tom.

"Tom Ogilvy" wrote:

If sortCriteria = "Ascending" Then rng.sort
Key1:=rng.Columns(ColNo).Cells(1,1), _
Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom: Exit Sub

--
Regards,
Tom Ogilvy


"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