Inserting a tab

[posting7]

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

 

[Bob Phillips]

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)

 

[Dave Peterson]

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

 

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

 

[posting7]

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