ExcelBanter - Sheet names in a macro

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Sheet names in a macro (https://www.excelbanter.com/excel-programming/358963-sheet-names-macro.html)

Sheet names in a macro

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

Sheet names in a macro

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

Sheet names in a macro

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

Sheet names in a macro