Non Sheet Specific Sort Macro

  • Thread starter Thread starter navel151
  • Start date Start date
N

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.
 
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
 
It would be good to see the code and to know if you placed it in a General
or a Sheet module
best wishes
 
Back
Top