Workbook Macro to Change Sheet Name based on Multiple Data

[Zanatos]

Hey Guys,

I have several worksheets that are auto-generated by a internet application, and given generic sheet tab name titles. I need to change these sheet tab names, but there are far to many to do by hand. I know you can make a macro for a specific sheet to rename the tab, I need a macro for "ThisWorkbook" to run through the entire worksheet and change these names for me, and I need to do it off based off multiple cells in each individual worksheet (shown below).

I want the name of each sheet to be MM-DD - Time am/pm
Each individual sheet has the time on it, in cell A2, but this is army time (0=12 am, 1=1 am, etc).
The date is also on each page, in cell C3:BJ3 (one giant merged cell, the date is only shown once - don't know if this will effect it).

Example:
A1 = 14
CJ = 07/01/2007

Sheet name needs to be = 07-01 - 2 pm

Any ideas?
Any and all help is appericatted.
Thanks.

 

[Zanatos]

OK

So, I am thinking of how to do this. Right now I have the following code in my "ThisWorkbook" page in VB code.



Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Source As Range)
'Put code in ThisWorkbook, not is module.
'It will work the next time you open the workbook.
'Change Range("A1") to cell with name in it.
If Range("A2") = "" Then
Exit Sub
Else
ActiveSheet.Name = Range("A2")
End If
End Sub


This will put in the time, whenever i change something. If I change the Private Sub Workbook_Open, nothing happens. Also, if I could just put something like


ActiveSheet.Name = "07-09" & Range("A2")


That would add the date with very little problems.

Any ideas on where I am going wrong?