Using array instead of different subs in vba

Question

I have developed a vba for many systems. I am giving examples for 2 systems here:

Private Sub Macro1()

Set x = Workbooks.Open("C:\Users\****\Desktop\New folder\System1.xls")

With x.Sheets("System1")

Set aCell1 = .Range("A1:X1000").Find(What:="User", LookIn:=xlValues, 
LookAt:=xlWhole, _MatchCase:=False, SearchFormat:=False)

.Range(aCell1, 
.Cells(.Rows.Count,aCell1.Column).End(xlUp)).Offset(2,0).Copy_
ThisWorkbook.Sheets("System1").Range("A2")
End With

Private Sub Macro2()

Set x = Workbooks.Open("C:\Users\****\Desktop\New folder\System2.xls")

With x.Sheets("System2")

Set aCell1 = .Range("A1:X1000").Find(What:="User", LookIn:=xlValues, 
LookAt:=xlWhole, _MatchCase:=False, SearchFormat:=False)

.Range(aCell1, 
.Cells(.Rows.Count,aCell1.Column).End(xlUp)).Offset(2,0).Copy_
ThisWorkbook.Sheets("System1").Range("A2")
End With

Is there a way through which I can mention all the system name in an array or list instead of writing different subs for different systems? Since the only thing that is getting changed is the system number

Answer 1

Try to add the system as a parameter. In the example below it is an optional one:

Option Explicit

Private Sub Macro1(Optional strParam As String = "System1")

    Set x = Workbooks.Open("C:\Users\****\Desktop\New folder\" & strParam & ".xls")

    With x.Sheets(strParam)

        Set aCell1 = .Range("A1:X1000").Find(What:="User", LookIn:=xlValues,
        LookAt:=xlWhole, _MatchCase:=False, SearchFormat:=False)

        .Range(aCell1,
        .Cells(.Rows.Count,aCell1.Column).End(xlUp)).Offset(2,0).Copy_
        ThisWorkbook.Sheets(strParam).Range ("A2")
    End With

End Sub

And this is how you call it:

Public Sub TestMe()

    Macro1 'same as Macro1 "System1"
    Macro1 "System1"
    Macro1 "System2"

End Sub

Edit: As far as you want array in the original, here is a possible solution, that you can fix using the Macro1 sub from above:

Public Sub TestMe()

    Dim myArr           As Variant
    Dim lngCounter      As Long

    myArr = Array("System1", "System2", "System3")

    For lngCounter = LBound(myArr) To UBound(myArr)
        Macro1 myArr(lngCounter)
    Next lngCounter

End Sub

Answer 2

You just need to refactor your code:

Private Sub Macro1()

GetData 1
End Sub

Private Sub Macro2()

GetData 2
End Sub

Sub GetData(systemNum as long)
Set x = Workbooks.Open("C:\Users\****\Desktop\New folder\System" & systemNum & ".xls")

With x.Sheets("System" & systemNum)

Set aCell1 = .Range("A1:X1000").Find(What:="User", LookIn:=xlValues, 
LookAt:=xlWhole, _MatchCase:=False, SearchFormat:=False)

.Range(aCell1, 
.Cells(.Rows.Count,aCell1.Column).End(xlUp)).Offset(2,0).Copy_
ThisWorkbook.Sheets("System1").Range("A2")
End With
End Sub