CODEWITHSUNDEEP
vbaexcel
dzmkr
dzmkr

Reputation: 17

Deleting range and creating AutoFilters in multiple tabs

I wanted to write a macro which will create AutoFilter, if not present, drop filters and delete specific range in some tabs in my workbook.

I created this code but somehow it doesn't work - data gis deleted only from one tab instead of all that are not listed in If statement. Please help!

Sub ClearTabs()

Dim rng As Range
Dim ws As Worksheet
Set rng = Range("B9:AK100")

For Each ws In ThisWorkbook.Sheets
    If ws.Name <> "lastfile" And ws.Name <> "Sheet1" And ws.Name <> "Sheet2" Then

        If Not ws.AutoFilterMode Then
            ws.Range("B8:AK8").AutoFilter
        End If
    ws.AutoFilter.ShowAllData
    rng.ClearContents

    End If

Next ws

End Sub

Upvotes: 0

Views: 43

Answers (2)

user3598756
user3598756

Reputation: 29421

you can avoid dimming and setting rng and go like this

Option Explicit

Sub ClearTabs()
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Sheets
        With ws
            If .Name <> "lastfile" And .Name <> "Sheet1" And .Name <> "Sheet2" Then
                If .AutoFilterMode Then
                    .AutoFilter.ShowAllData
                Else
                    .Range("B8:AK8").AutoFilter
                End If
                .Range("B9:AK100").ClearContents
            End If
        End With
    Next
End Sub

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33692

Following my comment above, You need to set the Range (Set rng = Range("B9:AK100")) inside the For Each ws In ThisWorkbook.Sheets loop.

Code:

Sub ClearTabs()

Dim rng As Range
Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
    With ws
        Select Case .Name
            Case "lastfile", "Sheet1", "Sheet2"
                ' do nothing
            Case Else
                Set rng = .Range("B9:AK100")

                If Not .AutoFilterMode Then
                    .Range("B8:AK8").AutoFilter
                End If
                .AutoFilter.ShowAllData
                rng.ClearContents
        End Select
    End With
Next ws

End Sub

Upvotes: 1

Related Questions