Workbook Macro to Change Sheet Name based on Multiple Data

Zanatos

Board Regular
Joined
May 18, 2007
Messages
65
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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Zanatos

Board Regular
Joined
May 18, 2007
Messages
65
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?
 

Forum statistics

Threads
1,181,717
Messages
5,931,646
Members
436,796
Latest member
parthibanmuniswamy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top