Sorting Sheet1 from Sheet2

[Guest]

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

 

[Bernie Deitrick]

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

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), _

 

[Tom Ogilvy]

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

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), _

 

[Guest]

Thanks Bernie and Tom.

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



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.
Key1:=Range(rng.Columns(ColNo).Address), _