sorting a different sheet when calling macro

zoog25

Active Member
Joined
Nov 21, 2011
Messages
418
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:
Code:
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?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Just remove this line
Code:
TPws.Range("A2" & ":AW" & lrow).Select
and I think you should be ok
 
Upvote 0
Hi
just replace
Code:
Set TPws = ThisWorkbook.Worksheets("Tract Parcels")

with
Code:
Set TPws = ThisWorkbook.Activesheet

Or
Code:
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
 
Last edited:
Upvote 0
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'
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top