Hi,

Here is a slight change in logic to the macro submitted by FSt1. It looks
to me like you want to clear the range on all sheets even if the renaming of
the sheet fails?

Assuming the macro is in the activeworkbook you don't need to explicitly
state that.

Option Explicit
Sub update_all_names3()
Dim sh As Worksheet
For Each sh In Worksheets
On Error Resume Next
sh.Name = sh.[A1]
If Err.Number < 0 Then
MsgBox sh.Name & " wasn't renamed!"
Err.Clear
End If
sh.[A2:I7].ClearContents
Next sh
End Sub

I may be wrong but I don't think the On Error GoTo 0 line serves any
function here.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"aussiegirlone" wrote:

OssieMac, Jacob Skaria, FSt1
I have tested all three suggestions on a new workbook, Jacob Skarias code
has an error 9 come up, but OssieMac and FSt1 both work well, and its just a
matter of choice. Such a difficult decision to make€¦.LOL Thankyou very much
for your help, but now, can I tempt you guys with my biggest project please?

The macro I used to clear the contents of 12 sheets, is not good enough to
clear 500 sheets. This project workbook also uses the code: Sub
update_all_names3(), to name all the sheets. Can you create a code that
would skip the first 14 sheets of the workbook and clear the contents of the
following 500 sheets, array A5:AF32 adding your suggested code
sh.Range("A2:I7").ClearContents code with it.
I would appreciate this very much if you could?

"Jacob Skaria" wrote:

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