Clever way to swap sheet names
 

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.

Clever way to swap sheet names
 

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


---
HTH

Bob Phillips

"Robert Crandal" wrote in message
...
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.

Clever way to swap sheet names
 

Assign a temporary name that you would never really use like Temp321.

Sub SwapNames(Sht1Name as string, Sh2Name as string)

Set Sht1 = sheets(Sht1Name)
Set Sht2 = sheets(Sht2Name)

Temp1 = Sht1.Name
Temp2 = Sht2.Name

Sht1.Name = "Temp321"
Sht2.name = Temp1
Sht1.Name = Temp2

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=166112

Microsoft Office Help

Clever way to swap sheet names
 

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

"Robert Crandal" wrote:

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.


.

Clever way to swap sheet names
 

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" wrote:

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.


.

Clever way to swap sheet names
 

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


"Bob Phillips" wrote in message
...
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

Clever way to swap sheet names
 

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!

Clever way to swap sheet names
 

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

Bob


"Robert Crandal" wrote in message
...
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!

Clever way to swap sheet names
 

"B Lynn B" wrote in message
...
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?

Clever way to swap sheet names
 

I am now interestingly confused by both of you.

Strange world. :-)



On Fri, 1 Jan 2010 11:51:52 -0000, "Bob Phillips"
wrote:

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

Bob


"Robert Crandal" wrote in message
...
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!

Clever way to swap sheet names
 

On Fri, 1 Jan 2010 11:52:34 -0000, "Bob Phillips"
wrote:


"B Lynn B" wrote in message
...
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?

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.