VBA code to run on multiple sheets.

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

Nila 02Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.