Hi All
I studied the posts but cannot seem to find an answer to my problem.

I have a spreadsheet that updates Engineers Job Details. Each Engineer
has his own worksheet. The tabs are all named accordingly for each
engineer.
On a menu page references to these worksheets are hyperlinked.
I also have some blank worksheets named NE3 and NE4 (NE-New Engineer)
ready for when a new Engineer starts.

If I need to change one of the Engineers name or place a new Engineers
name in I have referenced the Hyperlinks to a worksheet called Names so
I only have to update the names in the sheet to update all the
Hyperlinks. ‘So far so good’

Do I have to manual change the tab names? Which leads me to my problem
because I have macros that run 'on open' to sort all some of the
worksheets.
I have each sort for example,
Sub SortJohnRTF()
Application.ScreenUpdating = False
Sheets("JohnRTF").Activate
Range("A3:K299").Select
Selection.Sort Key1:=Range("B3"), Order1:=xlDescending,
Key2:=Range("A3") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("A2").Select
End Sub
I run these sorts on workbook open event.
If I want to change or implement a new name how can I change the names
in the macros.
I have tried to change the code to.
Sub SortSheet52()
Application.ScreenUpdating = False
Sheets("Sheet52").Activate
Range("A3:K299").Select
Selection.Sort Key1:=Range("B3"), Order1:=xlDescending,
Key2:=Range("A3") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("A2").Select
End Sub
But this does not work.
Basically I want to be able to change names without having to go
through every related macro and hyperlink.
Sorry long winded but trying to explain
Bernard


--
bern
------------------------------------------------------------------------
bern's Profile: http://www.excelforum.com/member.php...o&userid=20169
View this thread: http://www.excelforum.com/showthread...hreadid=533158

where sheet52 is typed into cell a11, something like this

Sheets(Range("a11").Value).Range("A3:K299").Sort _
Key1:=sheets(range("a11").value)Range("B3"), _
Order1:=xlDescending,Key2:=sheets(range("a11.value ).Range("A3") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

or even shorter maybe
with Sheets(Range("a11").Value)
..Range("A3:K299").Sort Key1:=.Range("B3"), _
Order1:=xlDescending,Key2:=.Range("A3") _
,Order2:=xlAscending, Header:=xlGuess
end with
--
NOT TESTED

Don Guillett

SalesAid Software

"bern" wrote in message
...

Hi All
I studied the posts but cannot seem to find an answer to my problem.

I have a spreadsheet that updates Engineers Job Details. Each Engineer
has his own worksheet. The tabs are all named accordingly for each
engineer.
On a menu page references to these worksheets are hyperlinked.
I also have some blank worksheets named NE3 and NE4 (NE-New Engineer)
ready for when a new Engineer starts.

If I need to change one of the Engineers name or place a new Engineers
name in I have referenced the Hyperlinks to a worksheet called Names so
I only have to update the names in the sheet to update all the
Hyperlinks. ‘So far so good’

Do I have to manual change the tab names? Which leads me to my problem
because I have macros that run 'on open' to sort all some of the
worksheets.
I have each sort for example,
Sub SortJohnRTF()
Application.ScreenUpdating = False
Sheets("JohnRTF").Activate
Range("A3:K299").Select
Selection.Sort Key1:=Range("B3"), Order1:=xlDescending,
Key2:=Range("A3") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("A2").Select
End Sub
I run these sorts on workbook open event.
If I want to change or implement a new name how can I change the names
in the macros.
I have tried to change the code to.
Sub SortSheet52()
Application.ScreenUpdating = False
Sheets("Sheet52").Activate
Range("A3:K299").Select
Selection.Sort Key1:=Range("B3"), Order1:=xlDescending,
Key2:=Range("A3") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("A2").Select
End Sub
But this does not work.
Basically I want to be able to change names without having to go
through every related macro and hyperlink.
Sorry long winded but trying to explain
Bernard


--
bern
------------------------------------------------------------------------
bern's Profile:
http://www.excelforum.com/member.php...o&userid=20169
View this thread: http://www.excelforum.com/showthread...hreadid=533158

If you go into the VBE (alt+F11), you see in the project explorer, two names
for each sheet.

Sheet1 (NE3)

for example

the Sheet21 is the code. name.

So you can use

set sh = Sheet1

from the immediate window:

sh = Sheet1
?sh.name
NE3
? sh.codename
Sheet1

In your code/macros, you can hardcode the CodeName


Sub SortJohnRTF()
Dim sh as Worksheet
Application.ScreenUpdating = False
set sh = Sheet5
sh.Activate
Range("A3:K299").Select
Selection.Sort Key1:=Range("B3"), Order1:=xlDescending,
Key2:=Range("A3") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("A2").Select
End Sub

--
Regards,
Tom Ogilvy




"bern" wrote in message
...

Hi All
I studied the posts but cannot seem to find an answer to my problem.

I have a spreadsheet that updates Engineers Job Details. Each Engineer
has his own worksheet. The tabs are all named accordingly for each
engineer.
On a menu page references to these worksheets are hyperlinked.
I also have some blank worksheets named NE3 and NE4 (NE-New Engineer)
ready for when a new Engineer starts.

If I need to change one of the Engineers name or place a new Engineers
name in I have referenced the Hyperlinks to a worksheet called Names so
I only have to update the names in the sheet to update all the
Hyperlinks. 'So far so good'

Do I have to manual change the tab names? Which leads me to my problem
because I have macros that run 'on open' to sort all some of the
worksheets.
I have each sort for example,
Sub SortJohnRTF()
Application.ScreenUpdating = False
Sheets("JohnRTF").Activate
Range("A3:K299").Select
Selection.Sort Key1:=Range("B3"), Order1:=xlDescending,
Key2:=Range("A3") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("A2").Select
End Sub
I run these sorts on workbook open event.
If I want to change or implement a new name how can I change the names
in the macros.
I have tried to change the code to.
Sub SortSheet52()
Application.ScreenUpdating = False
Sheets("Sheet52").Activate
Range("A3:K299").Select
Selection.Sort Key1:=Range("B3"), Order1:=xlDescending,
Key2:=Range("A3") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("A2").Select
End Sub
But this does not work.
Basically I want to be able to change names without having to go
through every related macro and hyperlink.
Sorry long winded but trying to explain
Bernard


--
bern
------------------------------------------------------------------------
bern's Profile:
http://www.excelforum.com/member.php...o&userid=20169
View this thread: http://www.excelforum.com/showthread...hreadid=533158

Hi All
Many thanks guys
Sorted it, once again thanks to your invalueable help.
Instead of:

Sub SortSheet52()
Application.ScreenUpdating = False
SHEETS(\"SHEET52\").ACTIVATE
RANGE(\"A3:K299\").SELECT
Selection.Sort Key1:=Range("B3"), Order1:=xlDescending,
Key2:=Range("A3") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("A2").Select
End Sub

Used the

Sub SortSheet52()
Dim sh as Worksheet
Application.ScreenUpdating = False
SET SH = SHEET52
SH.ACTIVATE
Range("A3:K299").Select
Selection.Sort Key1:=Range("B3"), Order1:=xlDescending,
Key2:=Range("A3") _, Order2:=xlAscending, Header:=xlGuess,
OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("A2").Select
End Sub


Once again thanks
:)
Bern


--
bern
------------------------------------------------------------------------
bern's Profile: http://www.excelforum.com/member.php...o&userid=20169
View this thread: http://www.excelforum.com/showthread...hreadid=533158

or from anywhere in the workbook withOUT selecting

Sub SortSheet52()
with sheets("sheet52").Range("A3:K299")
..Sort Key1:=.Range("B3"), Order1:=xlDescending,Key2:=.Range("A3") _
,Order2:=xlAscending, Header:=xlGuess,OrderCustom:=1
end with
End Sub


--
Don Guillett
SalesAid Software

"bern" wrote in message
...

Hi All
Many thanks guys
Sorted it, once again thanks to your invalueable help.
Instead of:

Sub SortSheet52()
Application.ScreenUpdating = False
SHEETS(\"SHEET52\").ACTIVATE
RANGE(\"A3:K299\").SELECT
Selection.Sort Key1:=Range("B3"), Order1:=xlDescending,
Key2:=Range("A3") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("A2").Select
End Sub

Used the

Sub SortSheet52()
Dim sh as Worksheet
Application.ScreenUpdating = False
SET SH = SHEET52
SH.ACTIVATE
Range("A3:K299").Select
Selection.Sort Key1:=Range("B3"), Order1:=xlDescending,
Key2:=Range("A3") _, Order2:=xlAscending, Header:=xlGuess,
OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("A2").Select
End Sub


Once again thanks
:)
Bern


--
bern
------------------------------------------------------------------------
bern's Profile:
http://www.excelforum.com/member.php...o&userid=20169
View this thread: http://www.excelforum.com/showthread...hreadid=533158