Excel data collection from multiple sheets

Question

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 :)

Tdi Ger · Accepted Answer · 2021-10-20 00:46:36Z

1

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.

answered Oct 20, 2021 at 0:46

Tdi Ger

815 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel data collection from multiple sheets

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related