Clever way to swap sheet names

[Robert Crandal]

I'm looking for a clever or efficient or cool way to
swap the names of 2 sheets using just one function
or subroutine call.

Suppose my workbook contains one sheet that
is named "foo" and the other is named "foo2".
I would want my function call to swap the names
of both sheets. If the function is called again,
it should reverse the process.

Everybody here always seem to have better or
shorter methods than mine, so I would greatly
appreciate your ideas.

thank you everyone.

 

[Bob Phillips]

Not much you can do cleverly I would have thought, but here is one way

Public Sub Test()
Call SwapNames(Worksheets("Sheet2"), Worksheets("Sheet3"))
End Sub

Public Function SwapNames(sh1 As Worksheet, sh2 As Worksheet)
Dim tmp As String

tmp = sh1.Name
sh1.Name = sh2.Name & "_"
sh2.Name = tmp
sh1.Name = Left(sh1.Name, Len(sh1.Name) - 1)
End Function

 

[Mike H]

Hi,

Not to sure about clever or cool but this will toggle the names of the first
and second sheets in a workbook

Sub Foo_Foo2()
Set firstsheet = Sheets(1)
Set secondsheet = Sheets(2)
firstsheet.Name = firstsheet.Name & secondsheet.Name
secondsheet.Name = Mid(firstsheet.Name, 1, _
Len(firstsheet.Name) - Len(secondsheet.Name))
firstsheet.Name = Right(firstsheet.Name, _
Len(firstsheet.Name) - Len(secondsheet.Name))
End Sub

Mike

 

[B Lynn B]

I thought it might also be handy to make it work for any two sheets you have
selected...

Sub NameSwap()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim nameholder As String

If ActiveWindow.SelectedSheets.Count = 2 Then
Set ws1 = ActiveWindow.SelectedSheets(1)
Set ws2 = ActiveWindow.SelectedSheets(2)
nameholder = ws1.Name
ws1.Name = ws2.Name & "_"
ws2.Name = nameholder
ws1.Name = Replace(ws1.Name, "_", "")
End If

End Sub

 

[Robert Crandal]

Hi Bob. Do you know if there's a way that this swap function will
work without reordering the tab positions after the swap occurs??
I prefer that the tabs stay in place and only their names get
swapped.

thank you

 

[Robert Crandal]

Hi Bob. Do you know if there's a way that this swap function will

work without reordering the tab positions after the swap occurs??
I prefer that the tabs stay in place and only their names get
swapped.

Please disregard the above question.... I thought the tabs were
changing order, when in reality just the names were changing.
I think I had a "duh" moment there, hahahaha!

 

[Bob Phillips]

Phew! You had me confused when I read the first one.

Bob

 

[Bob Phillips]

I thought it might also be handy to make it work for any two sheets you
have
selected...

Isn't that what they all do?

 

[CellShocked]

I am now interestingly confused by both of you.

Strange world.

 

[CellShocked]

Isn't that what they all do?

Note that by "selected" she means that two of your sheets, or tabs need
to be "selected" or "active" as recognized by Excel itself.

The other methods make no mention of a need for anyone to highlight or
"select" two sheets first. That is... since you only gave two sheets.

Her job works on a workbook with several worksheets to "choose" from.