Excel data collection from multiple sheets-CodePudding

firstly i am a beginner so i apologies in advance if anything is not clear. so basically i have one excel workbook that collects values (basically sum) from tables in 4 other workbooks (all workbooks exist in the same file and identified as 1 to 4 as an example), currently am using a macro to collect and sum the values (sample shown below).

    Range("D4").Select
ActiveCell.FormulaR1C1 = "=[1.xlsm]Sheet1!R4C3 RC   [2.xlsm]Sheet1!R4C3 RC   [3.xlsm]Sheet1!R4C3 RC   [4.xlsm]Sheet1!R4C3 RC"
Range("D5").Select
ActiveCell.FormulaR1C1 = "=[1.xlsm]Sheet1!R5C3 RC   [2.xlsm]Sheet1!R5C3 RC   [3.xlsm]Sheet1!R5C3 RC   [4.xlsm]Sheet1!R5C3 RC"
Range("D6").Select
ActiveCell.FormulaR1C1 = "=[1.xlsm]Sheet1!R6C3 RC   [2.xlsm]Sheet1!R6C3 RC   [3.xlsm]Sheet1!R6C3 RC   [4.xlsm]Sheet1!R6C3 RC"

as you can see the current method is a little messy. and i was wondering if there is a simpler way to make it work without having to manually mention the names of all the workbooks (like create an array with all the xlsm files in the same path)

thank you in advance and hope i wasn't confusing :)

CodePudding user response：

Try this code:

Sub Test()

Dim MyArr() As String, CurRow As Long, i As Integer, SubFormula As String, TotFormula As String, OutSheet As Worksheet

Set OutSheet = ActiveSheet

MyArr = Split("1.Xlsm,2.Xlsm,3.Xlsm,4.Xlsm", ",")

For CurRow = 4 To 6
   For i = LBound(MyArr) To UBound(MyArr)
      SubFormula = "["   MyArr(i)   "]Sheet1!R"   Trim(Str(CurRow))   "C3 RC"
      If i = LBound(MyArr) Then TotFormula = "="   SubFormula Else TotFormula = TotFormula   " "   SubFormula
   Next i
   OutSheet.Cells(CurRow, 4).FormulaR1C1 = TotFormula
Next CurRow

End Sub

There are many ways to solve such a problem, depending on the circumstances for sure also better ones, but it should show you the way how to solve this.