Is there a way to avoid repeating the same code in multiple Subs with variables?

Question

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

Too vague to answer in any detail, but code which is copy-pasted is typically a sub or function waiting to be born. — John Coleman, Nov 03 '21 at 01:59

Rohit Sai Janga · Accepted Answer · 2021-11-03T02:03:59.533

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

Side notes for OP: [avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). and the `s123 = s123_Arg`, `s456 = s456_Arg`, `s789 = x789_Arg` are redundant. Just use `Criteria1:=s123_Arg`, `Criteria1=x789_Arg`, and `Criteria1:=s456_Arg`. Also, `Call` can be removed: `OpenFilter "<>ABC", "<>*ABC*", "ABC", "C:\Users\Desktop\ABC Today.xlsx"`, without parentheses. — BigBen, Nov 03 '21 at 03:08

Is there a way to avoid repeating the same code in multiple Subs with variables?

1 Answers1