troubleshooting Question

VBA code to run on multiple sheets.

Avatar of Nila 02
Nila 02 asked on
VBAMicrosoft ExcelMicrosoft Office
3 Comments2 Solutions206 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros