I have a vba module that I am trying to make look more professional by removing code that's cut and pasted across nine individual Subs with separate variables for each of the nine. Is there a good way to avoid cutting and pasting? I've put the beginning portions of just a few of the nine Subs below; each Sub has about 300 lines that's copied (but not included in this question for space).
Sub OpenFilterABC()
Dim rng As Range
Dim s123 As String
Dim s456 As String
Dim x789 As String
s123 = "<>ABC"
s456 = "<>*ABC*"
x789 = "ABC"
Workbooks.Open Filename:="C:\Users\Desktop\ABC Today.xlsx"
Sheets("First Sheet").Select
Set rng = Range("$A$1:$ZZ$157000")
rng.AutoFilter Field:=7, Criteria1:=s123
Sheets("Second Sheet").Select
Set rng = Range("$A$1:$ZZ$157000")
rng.AutoFilter Field:=7, Criteria1:=x789
rng.AutoFilter Field:=24, Criteria1:=s456
'There are an additional 300 lines per Sub, plus nine total Subs; just shortened it for this question
End Sub
Sub OpenFilterDEF()
Dim rng As Range
Dim s123 As String
Dim s456 As String
Dim x789 As String
s123 = "<>DEF"
s456 = "<>*DEF*"
x789 = "DEF"
Workbooks.Open Filename:="C:\Users\Desktop\DEF Today.xlsx"
Sheets("First Sheet").Select
Set rng = Range("$A$1:$ZZ$157000")
rng.AutoFilter Field:=7, Criteria1:=s123
Sheets("Second Sheet").Select
Set rng = Range("$A$1:$ZZ$157000")
rng.AutoFilter Field:=7, Criteria1:=x789
rng.AutoFilter Field:=24, Criteria1:=s456
End Sub
Sub OpenFilterGHI()
Dim rng As Range
Dim s123 As String
Dim s456 As String
Dim x789 As String
s123 = "<>GHI"
s456 = "<>*GHI*"
x789 = "GHI"
Workbooks.Open Filename:="C:\Users\Desktop\GHI Today.xlsx"
Sheets("First Sheet").Select
Set rng = Range("$A$1:$ZZ$157000")
rng.AutoFilter Field:=7, Criteria1:=s123
Sheets("Second Sheet").Select
Set rng = Range("$A$1:$ZZ$157000")
rng.AutoFilter Field:=7, Criteria1:=x789
rng.AutoFilter Field:=24, Criteria1:=s456
End Sub
CodePudding user response:
You can create a generic function and pass the variables. And then call the functions as necessary.
Sub OpenFilter( s123_Arg As String, s456_Arg As String,x789_Arg As String, Filename_Arg As String)
Dim rng As Range
Dim s123 As String
Dim s456 As String
Dim x789 As String
s123 = s123_Arg
s456 = s456_Arg
x789 = x789_Arg
Workbooks.Open Filename:=Filename_Arg
Sheets("First Sheet").Select
Set rng = Range("$A$1:$ZZ$157000")
rng.AutoFilter Field:=7, Criteria1:=s123
Sheets("Second Sheet").Select
Set rng = Range("$A$1:$ZZ$157000")
rng.AutoFilter Field:=7, Criteria1:=x789
rng.AutoFilter Field:=24, Criteria1:=s456
End Sub
Sub OpenFilterABC()
Call OpenFilter("<>ABC","<>*ABC*","ABC","C:\Users\Desktop\ABC Today.xlsx")
End Sub