VBA code to run on multiple sheets.

Nila 02
Nila 02 used Ask the Experts™
on
Hi, I have a VBA/macro code here which autofilters the values in activesheet. My aim is to actually make the VBA code run for the sheets i specify.

Aim: When i press the form button it should filter the values in both the sheets and not just active sheet.

The methods i tried are not working, it would be really helpful if someone could help. Here is the code i have been using.

Sheets i want it to run on : "Prev Month" and "Current Month"


Sub Macro1()
'
' Macro1 Macro
'

'
Dim xWs As Worksheet
 Dim rng As Range, res As Variant
    
If xWs.Name <> "Prev Month" And xWs.Name <> "Current Month" Then
  
Range("A1").Select

Selection.AutoFilter
    
   
Set rng = Worksheet.AutoFilter.Range.Rows(1)

res = Application.Match("Diaper Range Premium (1) Mainline (2)", rng, 0)

rng.AutoFilter Field:=res, Criteria1:="MAINLINE"

Dim rng1 As Range, res1 As Variant
    
    
Set rng1 = Worksheet.AutoFilter.Range.Rows(1)

res1 = Application.Match("Diaper Type (1) - Taped (2) - Pants (9) - Unspecified", rng1, 0)

rng1.AutoFilter Field:=res1, Criteria1:="PANTS"

End If

End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
The statement If xWs.Name <> "Prev Month" And xWs.Name <> "Current Month" Then is not doing what you are trying to do.
It should be If xWs.Name = "Prev Month" OR xWs.Name = "Current Month" Then

The following code will work on a sheet if it's name is either Prev Month or Current Month.

Sub Macro1()
Dim xWs As Worksheet
Dim rng As Range, res As Variant
    
For Each xWs In Worksheets
    If xWs.Name = "Prev Month" Or xWs.Name = "Current Month" Then
        xWs.AutoFilterMode = False
        With xWs.Rows(1)
            Set rng = xWs.Rows(1)
            res = Application.Match("Diaper Range Premium (1) Mainline (2)", rng, 0)
            .AutoFilter Field:=res, Criteria1:="MAINLINE"
            res = Application.Match("Diaper Type (1) - Taped (2) - Pants (9) - Unspecified", rng, 0)
            .AutoFilter Field:=res, Criteria1:="PANTS"
        End With
    End If
Next xWs
End Sub

Open in new window

Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
I think you should also account for the errors, try this instead...
Sub Macro1()
Dim xWs As Worksheet
Dim rng As Range, res As Variant
    
For Each xWs In Worksheets
    If xWs.Name = "Prev Month" Or xWs.Name = "Current Month" Then
        xWs.AutoFilterMode = False
        With xWs.Rows(1)
            Set rng = xWs.Rows(1)
            res = Application.Match("Diaper Range Premium (1) Mainline (2)", rng, 0)
            If Not IsError(res) Then .AutoFilter Field:=res, Criteria1:="MAINLINE"
            res = Application.Match("Diaper Type (1) - Taped (2) - Pants (9) - Unspecified", rng, 0)
            If Not IsError(res) Then .AutoFilter Field:=res, Criteria1:="PANTS"
        End With
    End If
Next xWs
End Sub

Open in new window

ste5anSenior Developer
Commented:
So you're a writing actually a macro in VBA?

In this case use a top-down approach to create clean code. This keeps the structure simple and self-explaining. It also allows more flexibility in code reuse.

Variable declarations are always placed in the beginning of a method. For better readability object variables types should contain the library name.

Thus your macro becomes (untested):

Option Explicit

Public Sub AutfilterAll()

  Dim ws As Excel.Worksheet
  
  If GetSheetByName("Prev Month", ws) Then
    AutofilterWorksheet ws, "Diaper Range Premium (1) Mainline (2)", "MAINLINE"
    AutofilterWorksheet ws, "Diaper Type (1) - Taped (2) - Pants (9) - Unspecified", "PANTS"
  End If
  
  If GetSheetByName("Current Month", ws) Then
    AutofilterWorksheet ws, "Diaper Range Premium (1) Mainline (2)", "MAINLINE"
    AutofilterWorksheet ws, "Diaper Type (1) - Taped (2) - Pants (9) - Unspecified", "PANTS"
  End If
    
  Set ws = Nothing
  
End Sub

Private Sub AutofilterWorksheet(AWorksheet As Excel.Worksheet, AMatch As String, ACriteria As String)

  Dim rng As Excel.Range
 
  Dim res As Variant

  AWorksheet.Range("A1").AutoFilter
  Set rng = AWorksheet.AutoFilter.Range.Rows(1)
  res = Application.Match(AMatch, rng, 0)
  rng.AutoFilter Field:=res, Criteria1:=ACriteria
  Set rng = Nothing
  
End Sub

Private Function GetSheetByName(AName As String, ByRef AWorksheet As Excel.Worksheet) As Boolean

  On Local Error GoTo LocalError

  GetSheetByName = False
  Set AWorksheet = ActiveWorkbook.Sheets(AName)
  GetSheetByName = True
  Exit Function
  
LocalError:
  GetSheetByName = False

End Function

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial