How can I clear a range of cells in multiple worksheets in the same workbook?

[haganator]

I have a workbook with 14 worksheets in it, and I need to clear contents with the same range in each worksheet. Here is the code that I'm trying to use:

Dim Sht As Worksheet

On Error Resume Next

For Each Sht In ThisWorkbook.Worksheets
Range("C22:H1461").Select
Selection.ClearContents
Range("C22").Select
Next Sht


End Sub
​

The code only works on the active sheet...I can't get it to go to the next sheet.

Any ideas would be greatly appreciated.

Thanks in advance,
Jeff

 

[hiker95]

haganator


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub Test()
Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Worksheets
  Sht.Activate
  Range("C22:H1461").ClearContents
  Range("C22").Select
Next Sht
End Sub

Or, try, this next piece of code, but this will not work Range("C22").Select :

Option Explicit
Sub Test2()
Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Worksheets
  Sht.Range("C22:H1461").ClearContents
Next Sht
End Sub

 

[haganator]

Worked like a charm. Thank you very much, hiker95!!

 

[hiker95]

haganator,

Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.