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


---
HTH

Bob Phillips

"Robert Crandal" <(E-Mail Removed)> wrote in message
news:_C__m.2$(E-Mail Removed)...
> 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.
>
>

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.
>
>
> .
>

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.
>
>
> .
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 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!

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

Bob


"Robert Crandal" <(E-Mail Removed)> wrote in message
news:SE8%m.1846$_(E-Mail Removed)...
>> 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!
>
>

"B Lynn B" <(E-Mail Removed)> wrote in message
news:9CED5C2D-F400-4812-9590-(E-Mail Removed)...
>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?

I am now interestingly confused by both of you.

Strange world. :-)



On Fri, 1 Jan 2010 11:51:52 -0000, "Bob Phillips"
<(E-Mail Removed)> wrote:

>Phew! You had me confused when I read the first one. :-)
>
>Bob
>
>
>"Robert Crandal" <(E-Mail Removed)> wrote in message
>news:SE8%m.1846$_(E-Mail Removed)...
>>> 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!
>>
>>
>

On Fri, 1 Jan 2010 11:52:34 -0000, "Bob Phillips"
<(E-Mail Removed)> wrote:

>
>"B Lynn B" <(E-Mail Removed)> wrote in message
>news:9CED5C2D-F400-4812-9590-(E-Mail Removed)...
>>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.