sorting a different sheet when calling macro

[zoog25]

Hello all,

I have created a custom sorting macro for one sheet on my workbook. The macro works great when i'm on that sheet. My problem is that i want to use the macro to run when being called from a different sheet. Here is the code i'm using:

Dim TPws As Worksheet
Dim lrow As Long


Set TPws = ThisWorkbook.Worksheets("Tract Parcels")


lrow = TPws.Cells(Rows.Count, 7).End(xlUp).Row


TPws.Range("A2" & ":AW" & lrow).Select
    ActiveWorkbook.Worksheets("Tract Parcels").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Tract Parcels").Sort.SortFields.Add Key:=Range("G2" & ":G" & lrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Tract Parcels").Sort.SortFields.Add Key:=Range("H2" & ":H" & lrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Tract Parcels").Sort
    .SetRange Range("A2" & ":AW" & lrow)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub

What do i need to do in order to allow me to use the above code when it is being called on a macro from a different active sheet?

 

[Fluff]

Just remove this line

TPws.Range("A2" & ":AW" & lrow).Select

and I think you should be ok

 

[mohadin]

Hi
just replace

Set TPws = ThisWorkbook.Worksheets("Tract Parcels")

with

Set TPws = ThisWorkbook.Activesheet

Or

sub test()
 With ActiveSheet    lrow = .Cells(Rows.Count, 7).End(xlUp).Row
    .Range("A2" & ":AW" & lrow).Select
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=Range("G2" & ":G" & lrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=Range("H2" & ":H" & lrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort
    .SetRange Range("A2" & ":AW" & lrow)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub

 

[zoog25]

Thank you guys, but i think there was a misunderstanding, the code is just made to sort sheet "Tract Parcels". I have another sheet that inputs information into "Tract Parcels" through a Macro called 'FMtoTP'. Now a line in 'FMtoTP' calls 'Quick_Update()'. Now 'FMtoTP' is triggered through a change event on sheet 'Final Map' so 'Final Map' would be the active sheet name. So how do i go with having 'Final Map' sort items in 'Tract Parcels'

 

[Fluff]

If you always want to sort the "Tract parcels" sheet, remove the line I pointed out & you should be ok.
Did you try it? If so what was the result?