On Mon, 29 Jun 2009 18:40:03 -0700, 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


-----------------------------------------------------------------------------
Less Spam Better enjoyable experience
Visit : news://spacesst.com


If there are only 12 sheets in the workbook, i.e. you want all sheets
to be cleared, try this:

Public Sub ClearRangeIn12Sheets()
Const csCellRef As String = "A2:I7"
Dim ws As Worksheet
For Each ws In Worksheets
ws.Range(csCellRef).ClearContents
Next ws
End Sub

If there are more than 12 sheets and you only want to clear the 12
first sheets, try this:

Public Sub ClearRangeIn12Sheets()
Const csCellRef As String = "A2:I7"
Dim ws As Worksheet
For i = 1 To 12
Set ws = Worksheets(i)
ws.Range(csCellRef).ClearContents
Next i
End Sub

Hope this helps / Lars-Åke