Non Sheet Specific Sort Macro

[navel151]

I had a sort macro set-up in Excel 2003 that worked on whatever sheet you
were on, but now have Excel 2007 and I cannot get a sort that is not specific
to the sheet that the macro was made on.

Does anyone know how to get the macro to be non sheet specific?

Thanks.

 

[Dave Peterson]

Maybe you could just change your code to point to the activesheet.

If I had headers in row 1 and data in rows 2:xxx and I could use column A to
determine the last row that should be included in the sort, then I could use:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim ActCell As Range
Dim LastCol As Long
Dim LastRow As Long
Dim myRng As Range

Set wks = ActiveSheet

Set ActCell = ActiveCell

With wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set myRng = .Range("A1", .Cells(LastRow, LastCol))
End With

If Intersect(ActCell, myRng) Is Nothing Then
MsgBox "Please select a cell in the range"
Exit Sub
End If

With myRng
.Cells.Sort key1:=.Columns(ActCell.Column), _
order1:=xlAscending, header:=xlYes
End With

End Sub

 

[Bernard Liengme]

It would be good to see the code and to know if you placed it in a General
or a Sheet module
best wishes

 

[Don Guillett]

As ALWAYS, post your code for comments