Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
This clever code ain't doing nothin, why? | Excel Programming | |||
sorting clever formula | Excel Worksheet Functions | |||
How do I swap a group of surnames to the front of first names? | Excel Discussion (Misc queries) | |||
I need a clever VLOOKUP formula | Excel Worksheet Functions | |||
trying to be clever | Excel Programming |