Clever way to swap sheet names

  • Thread starter Thread starter Robert Crandal
  • Start date Start date
R

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.
 
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
 
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
 
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
 
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
 
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.
Click to expand...

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!
 
Isn't that what they all do?
Click to expand...
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.
 
Back
Top