Work sheets tab names only replace part ot the present name

[John Augustein]

The work book has 23 sheets all linked to 1. I want to put the parts to change too.
A1 = 2482
A2 = CH5

The following is a current tab name.

2481~2481 SN22 CODEGK4

I need everything to stay the same except after the change I need it to say

2482~2482 SN22 CODECH5

Thanks in advance.
Augie

EggHeadCafe - Software Developer Portal of Choice
Review: Improving .NET Application Performance
http://www.eggheadcafe.com/tutorial...be8-33c02aac8b91/review-improving-net-ap.aspx

 

[Teethless mama]

=A1&"~"&A1&" SN22 CODE"&A2

 

[Rick Rothstein]

I assume you want to change the actual worksheet name, correct? If so...

You didn't say whether the number in A1 is always going to be four digits
long or not; nor did you day whether the value in A2 will always be three
characters long or not. This code should work for any case...

Sub ChangeTabName()
Dim S() As String
If Range("A1").Value <> "" And Range("A2").Value <> "" Then
S = Split(ActiveSheet.Name, , 2)
S(0) = Range("A1").Value & "~" & Range("A1").Value
S(1) = Left(S(1), InStrRev(S(1), "code", , vbTextCompare) _
+ 3) & Range("A2").Value
ActiveSheet.Name = Join(S)
End If
End Sub