Sort - need to restrict sort procedure on active sheet only?

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
need code for sort routine that will only let it work on the active sheet?

I know how to use macro recorder to get sort routine, but is that the best way to do it?
This message was edited by em on 2002-10-19 05:29
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Not sure I understand your question re: active sheet only...

And using the recorder is a good way of getting the code...though remember that it you don't have to use active sheet in the code, you can specify the sheet.
 

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
I have a file with 10 sheets that are identical except that they have data on them that applies to a particular unit.

So if a person selected their unit (they are looking at their sheet) I wanted to run a sort routine for the sheet they are looking at--would this be considered the active sheet?

So if I used the recorder to record the macro would that macro only work for that sheet they are looking at unless I specified another sheet??
 

kskinne

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
If you record a macro in one sheet, it will run in any of the sheets in your workbook, not just the sheet in which you recorded the macro. If you want a macro to run only in a specific sheet whenever the sheet is activated, place this code in that sheet's object window in the visual basic editor:

Private Sub worksheet_activate()
'Place your sort code here
End Sub

then this code would only run in that particular sheet

HTH
kevin
 

kskinne

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
another possible suggestion:
if each sheet contains information that only pertains to one specific user, you could set up your macro in each sheet so that the macro in that user's sheet will only run for that particular user, when they are viewing the spreadsheet at a machine where they are logged in. you would simply put the following code in each sheet of the workbook:

Private Sub worksheet_activate()
If Application.Username <> "place username here with quotes" Then End
'Place code for this sheet here
End Sub

for each sheet you will just change the username to the network login name for the person that this sheet pertains to.

kevin
 

Watch MrExcel Video

Forum statistics

Threads
1,123,088
Messages
5,599,664
Members
414,325
Latest member
kfg1287

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
Top