Loop thru some sheets in wkbk and call macro - better way?

[Mifty]

Hi everyone,

I've got a wkbk with 26 sheets. I need to clear contents on the same range
on each sheet but only on 20 of the sheets.

So I've got a ClearContents routine which I'm calling from the ClearAll
routine

Sub ClearAll()
Application.ScreenUpdating = False
Sheets("Data").Select
Call ClearContents
Sheets("DataDD").Select
Call ClearContents
etc

This is quite slow and I'm sure there must be a more efficient and elegant
way to code this.

Any suggestions would be welcome.

Thank you

 

[Greg Glynn]

Hi Mifty,

You might try excluding the names of the sheets which you know you
don't want to change:

Sub ConsolidateSheets()

'Macro by Greg Glynn - May 2007

Application.ScreenUpdating = False

Dim Wks As Worksheet
Sheets("PC Consolidated List").Range("A2:IV65536").Clear

For Each Wks In Worksheets
If Wks.Name <> "Divisions & Cost Centres" And _
Wks.Name <> "PC Consolidated List" And _
Wks.Name <> "Lookup Tables" Then

Wks.ClearContents

End If
Next Wks

Application.ScreenUpdating = True


End Sub

 

[Mifty]

Hi Greg,

Looks good.

Sorry I haven't answered before - having the week from hell!

Can this be altered to set data validation to ="" (2nd option in dropdown)?

I'm already posting this question further along in the forum.

Option Explicit
Sub Datavalreset()
Dim rng2 As Range ' specifies range in wksh
Dim rng As Range 'specifies type of cell i.e. special cells
Dim ws As Worksheet
Dim cell As Range

For Each ws In ActiveWorkbook.Worksheets

On Error Resume Next
Set rng = Range("A1:T45")
Set rng2 = rng.Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If Not rng2 Is Nothing Then

For Each cell In rng2

If cell.Validation.Type = xlValidateList Then
cell.Value = ""
End If
Next
Else
MsgBox "No Data Validation Cells"
End If
Next ws
End Sub
This code works when all sheets have data validation but does not pick up
range when some sheets don't. When I found this out I remembered that I
hadn't replied to your post - sorry and thought that you may be able to help
me

Many thanks