Merging 3 Workbooks without using macros

[Guest]

Hello,

There are three of us that keep track of data for our respective departments
in Excel but would like to merge only certain data into one master worksheet
so our boss can see a general snap shot as to what we are doing. Is it
possible to merge our workbooks automatically as we enter data? Our
worksheets are the same size. I know I can use a macro to do this, but it
only works when all the worksheets are in the same workbook. We each need to
be able to add data throughout the day and not worry that someone else is
using the worksheet. Any help is appreciated.

Thanks
Mark

 

[Harald Staff]

Hi Mark

So you don't want a macro because it can not access remote files ? That is
not quite true. This sample pulls data from the file C:\Temp\Book1.xls

Sub PullDataSample()
Dim R As Long, C As Long
For R = 1 To 100
For C = 1 To 10
Sheets(1).Cells(R, C).FormulaR1C1 = _
"='C:\Temp\[Book1.xls]Sheet1'!RC"
Sheets(1).Cells(R, C).Calculate
Sheets(1).Cells(R, C).Value = _
Sheets(1).Cells(R, C).Value
Next C
Next R
End Sub

HTH. Best wishes Harald