[RESOLVED] Summing multiple spreadsheets into 1

[Maver1ck666]

I have a number of spreadsheets in a folder which are all exactly the same called test1.xls, test2.xls, test3.xls etc.

Within each spreadsheet are 3 tabs. All the tabs are identically named and the first tab is replicated throughout all the spreadsheets.

What I am trying to do (but failing miserably) is to find a way of summing the first tab in each spreadsheet into a single tab on a central spreadsheet.

For Example:

test1.xls (TAB1)

Column A Column B Column C Column D
19 22 01 16
18 21 12 32

test2.xls (TAB1)

Column A Column B Column C Column D
32 25 41 01
10 05 12 02

test2.xls (TAB1)

Column A Column B Column C Column D
05 21 49 17
19 15 02 21


SUMMARY.xls (TAB1) - this tab will sum all the cells from the above examples and keep the formatting the same:

Column A Column B Column C Column D
56 66 91 34
48 41 26 55

Any suggestions please?

Mav

[westconn1]

what about a formula in the summary worksheet?

=sum('[test1.xls]tab1'!a1, '[test2.xls]tab1'!a1, '[test3.xls]tab1'!a1)

copy the formula into the required range

[Maver1ck666]

I was thinking of using a formula but I would have to do it in over 10,000 cells so thought it would be easier to do it in vba. There are also about 15 spreadsheets so would take me an age to do

[westconn1]

you only need to put in 1 cell then drag

[SQLADOman]

Hi Maver1ck666

How about something like this to fill the range you want with the formula westconn1 provided.

Sub Fill_Column_Range_with_Formula()

Dim rng As Range
Dim wks As Object

Set wks = Worksheets("tab1")

With wks
    Set rng = .Range("A1:A3")
End With
       
     rng.ClearContents
     
     rng.NumberFormat = "General"
      
     rng.Formula = "=sum('[test1.xls]tab1'!a1, '[test2.xls]tab1'!a1, '[test3.xls]tab1'!a1)"
    
End Sub

[Maver1ck666]

I opted in the end to hard link the spreadsheets in the main stats sheet. Cheers for all your help though