Try the below macro..which works (clear contents and rename) from sheet1 to
sheet12. Hope Range("A1") will not have characters like "\" "/" etc which are
not accepted as sheet names

Sub Macro()
Dim intTemp As Integer
For intTemp = 1 To 12
Sheets("Sheet" & intTemp).Range("A2:I7").ClearContents
Sheets("Sheet" & intTemp).Name = _
Sheets("Sheet" & intTemp).Range("A1")
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"aussiegirlone" wrote:

Below are two macros that work just beautifully. The problem is when I use
the macro to RenameSheetsTabs, the macro ClearContents does not work unless I
edit the worksheets array and change the names in that section.
Is there a way that the RenameSheetTabs macro will also Rename the names in
the ClearContents macro or is this impossible.

ClearContentsMacro

Public Sub ClearRangeIn12Sheets()
Const csCellRef As String = "A2:I7"
Dim ws As Worksheet
For Each ws In Worksheets(Array("Sheet1", "Sheet2", "Sheet3",
"Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", "Sheet9", "Sheet10",
"Sheet11", "Sheet12"))
ws.Range(csCellRef).ClearContents
Next ws
End Sub

RenameSheetTabsMacro

Option Explicit
Sub update_all_names3()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
sh.Name = sh.Cells(1, 1).Value
If Err.Number < 0 Then
MsgBox sh.Name & " wasn't renamed!"
Err.Clear
End If
On Error GoTo 0
Next sh
End Sub