Presently I have a Workbook that contains about 120 tabs. They're all
named in Alphabetical order. A,B,C etc. They're all in order. My
problem is I need to insert a sheet between Z and AA. Therefore I need
the new sheet to be called AA and the old AA to be AB, the old AB to be
AC. . . etc. Each of these sheets have info on stores and it's
important that I have the stores in the correct order.

This is not a one time thing, it's possible I will have to remove a
sheet or two with time also.

Any ideas?

I did find chip pearson's sorting macro which is great but I really
need to mass rename almost all of my sheets (tabs).


Tracy

After inserting or deleting a sheet, ru this macro

Sub RenameSheets()
Dim i As Long
For i = 1 To Worksheets.Count
Worksheets(i).Name = i
Next i
For i = 1 To Worksheets.Count
Worksheets(i).Name = ColumnLetter(i)
Next i
End Sub

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(Col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


wrote in message
oups.com...
Presently I have a Workbook that contains about 120 tabs. They're all
named in Alphabetical order. A,B,C etc. They're all in order. My
problem is I need to insert a sheet between Z and AA. Therefore I need
the new sheet to be called AA and the old AA to be AB, the old AB to be
AC. . . etc. Each of these sheets have info on stores and it's
important that I have the stores in the correct order.

This is not a one time thing, it's possible I will have to remove a
sheet or two with time also.

Any ideas?

I did find chip pearson's sorting macro which is great but I really
need to mass rename almost all of my sheets (tabs).


Tracy

As long as you're using less than 256 worksheets (or 16384 with xl2007), this
macro would do the renames:

Option Explicit
Sub testme()
Dim wCtr As Long
Dim myName As String
For wCtr = 1 To Worksheets.Count
Worksheets(wCtr).Name = "Unique__" & wCtr
Next wCtr

For wCtr = 1 To Worksheets.Count
myName = Worksheets(1).Cells(1, wCtr).Address(0, 0)
myName = Left(myName, Len(myName) - 1)
Worksheets(wCtr).Name = myName
Next wCtr
End Sub

It renames each sheet to a new name, then renames those names to the final name.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

wrote:

Presently I have a Workbook that contains about 120 tabs. They're all
named in Alphabetical order. A,B,C etc. They're all in order. My
problem is I need to insert a sheet between Z and AA. Therefore I need
the new sheet to be called AA and the old AA to be AB, the old AB to be
AC. . . etc. Each of these sheets have info on stores and it's
important that I have the stores in the correct order.

This is not a one time thing, it's possible I will have to remove a
sheet or two with time also.

Any ideas?

I did find chip pearson's sorting macro which is great but I really
need to mass rename almost all of my sheets (tabs).

Tracy

--

Dave Peterson

ps.

Personally, I think you'd be better served by giving each worksheet a
significant name--even if it's just the date and time (yyyy_mm_dd or
yyyymmdd_hhmmss to make sorting easier).




wrote:

Presently I have a Workbook that contains about 120 tabs. They're all
named in Alphabetical order. A,B,C etc. They're all in order. My
problem is I need to insert a sheet between Z and AA. Therefore I need
the new sheet to be called AA and the old AA to be AB, the old AB to be
AC. . . etc. Each of these sheets have info on stores and it's
important that I have the stores in the correct order.

This is not a one time thing, it's possible I will have to remove a
sheet or two with time also.

Any ideas?

I did find chip pearson's sorting macro which is great but I really
need to mass rename almost all of my sheets (tabs).

Tracy

--

Dave Peterson

I totally agree on renaming the tabs as significant names. I am
actually going to recomment to my user that he do that but he might not
be willing, therefore I need a backup plan.


Tracy