Try the below..to clear the contents from sheet 15 to end

Sub Macro()
Dim intTemp As Integer
For intTemp = 15 To ActiveWorkbook.Sheets.Count
Sheets(intTemp).Range("A5:AF32").ClearContents
Next
End Sub

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


"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