Loop All Worksheets/Sheets in Workbook & Apply Code

NEW! More Books..

Add Excel Answers & Search To Your Google Toolbar Details

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Got any Excel Questions? Free Excel Help .

Lot's More: Excel VBA. See Also: Loop Through a Folder of Workbooks || Loop Through Worksheets || Add Worksheets in Month Order || Add Worksheets in Numeric Order || Sort Excel Sheets/Worksheets

Loop Through All Sheets

The code below Loops through all Worksheets in the active Workbook and clears cells with the Style name specified by the user. It uses an InputBox to collect the Style name. It also incorporates a Select Case Statement to exclude/include specified Sheets by CodeName. It also uses the UserInterFaceOnly argument of the Sheet Protection Method, so while code can run on protected sheets, normal users cannot change locked cells.

The other important element is the use of the SpecialCells Method to ensure only the cell the user wants (Formulae/Constants or both) are affected. This is asked of the user via the MsgBox Function.

The code is optimized for both speed and efficiency, which should not be confused with the shorter the code the faster and more efficient it is.

Naturally the code uses Styles only as an example, it could any code that uses cell Properties, Objects and Methods.

Sub ClearCellsWithStyleX()
Dim strStyleName As String
Dim lClearFormulae As Long
Dim wsheet As Worksheet
Dim rCcells As Range, rFcells As Range
Dim rLookin As Range, rCell As Range
Dim xlCal As XlCalculation


        'Collect Style name. Default is 1st in Index
        strStyleName = InputBox("Type the style name", _
            "CLEAR CELL WITH STYLE...", ThisWorkbook.Styles(1))
        If strStyleName = vbNullString Then Exit Sub 'Cancelled
        
        'Find out if formulae should be cleared
        lClearFormulae = MsgBox("Clear formulae cells with the Style " _
            & strStyleName, vbYesNo + vbQuestion)

        'Turn off error trapping
        On Error Resume Next
        'Pass the Calculation mode
        xlCal = Application.Calculation
        'Put calculation into manual
        Application.Calculation = xlCalculationManual
        'Turn off screenupdating
        Application.ScreenUpdating = False
    
        'Loop through all Worksheets
        For Each wsheet In Worksheets
           Select Case UCase(wsheet.CodeName)
                'List any sheets (UPPER CASE) to exclude here.
                Case "SHEET1", "SHEET5"
                    'Do optional code here
                Case Else
                    'Protect/reprotect so only code can affect _
                    locked cells.
                    wsheet.Protect Password:="Secret", _
                        UserInterFaceOnly:=True
                    With wsheet.UsedRange
                        'Set range variable to all constants
                        Set rCcells = .SpecialCells(xlCellTypeConstants)
                            'Formulae cells to be cleared.
                            If lClearFormulae = vbYes Then
                                'Set range variable to all formula cells.
                                Set rFcells = .SpecialCells(xlCellTypeFormulas)
                            End If
                    End With

                     'Reduce down range to look in
                    If rCcells Is Nothing And lClearFormulae = vbYes Then
                        Set rLookin = rFcells.Cells 'formulas
                    ElseIf rFcells Is Nothing Then
                        Set rLookin = rCcells.Cells 'constants
                    Else
                        Set rLookin = Application.Union(rFcells, rCcells) 'Both
                    End If
                    
                    'Loop for each cell in required range
                    For Each rCell In rLookin
                        'Change coding here to suit
                        If rCell.Style = strStyleName Then rCell.ClearContents
                    Next rCell
            End Select
        Next wsheet
        
        'Set calculation, screenupdating and error trapping back.
        Application.Calculation = xlCal
        Application.ScreenUpdating = True
        On Error GoTo 0
        
End Sub

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $70.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft Ž and Microsoft Excel Ž are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft