[RESOLVED] Clearing cells in a range?

This should be a piece of cake.

I have about 40 spreadsheets each with 12 worksheets (don't ask why - I inherited this :confused: )

Anyway, end of financial year - I need to clear all the data in a specifed range on all worksheets on all workbooks.

That's about 500 worksheets I need to delete - manually. :sick:

I'd much rather do 40 workbooks than 500 worksheets so I was going to copy and paste this bit of code into each workbook - run it - watch the data get zapped and then go :thumb:

Code:

---

Sub ClearWorksheet()
Dim ws As Worksheet

  For Each ws In ActiveWorkbook.Worksheets
      Worksheets(ws).Range("A5", "AN905").ClearContents
  Next
End Sub

---

Should be simple - not asking for much and I'm sure it's just a syntax thing I'm missing.

I intend on opening a workbook, pasting in the sub and then running it. Open next workbook etc etc.

Forgot to add :-

The first clearcontents works, that is, worksheet 1, but fails on the second

Run-time erro 1004
Application-defined or object-defined error

Yeah - ta. Thx for that error message. :(

I've even tried this

Code:

---

  Worksheets("Jul06").Range("A5:AN905").ClearContents
  Worksheets("Aug06").Range("A5:AN905").ClearContents
  Worksheets("Sept06").Range("A5:AN905").ClearContents

---

But again - fails on the second clearcontents attempt.

Hmm. Seems the worksheets are protected (no password).

BRB.

Sorted. I'll just paste and run this in each workbook. I'll be done by the time you read my ramblings. :)

Code:

---

Sub ClearWorksheet()
Dim ws As Worksheet

  For Each ws In ActiveWorkbook.Worksheets
      ws.Unprotect
      ws.Range("A5", "AN905").ClearContents
  Next
End Sub

---

Thanks for your help Gaz! :)