Updating a Master workbook from multiple workbooks (Excel 2000)

rpudney

New Member
Joined
Sep 2, 2009
Messages
11
Hi all,

I would like to know if there is a way in Excel 2000 (Sadly our organisation is rather behind on the times) to essentially do the following:

Books A,B & C are seperate workbooks that are all formatted in the same way (same number of columns and headings/titles,etc). The data in these workbooks has a seperate date and timestamp for each row of data but all three workbooks contain data where the date/time stamp is only 1 or 2 seconds. The dates and times for this data cover the same period and will continue to do so as they get updated over the next few months.

What I would like to do is to is create a Master workbook that collates the data from all three workbooks, sorts them in an ascending date, then time order and is capable of automatically updating itself with new data when new data is added to one or more of the seperate workbooks.

Having seperate workbooks is my preferred option however I have no issues with creating one workbook with multiple worksheets in it if there is an easier and faster way to do it that way.

Thank you in advance for your time,

RG
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

conny2254

Board Regular
Joined
Jun 9, 2009
Messages
248
Im not sure if this is working in excel 2000 but the following code loops through a folder and imports the data from the range B11:S29 (row by row). To use this I would put your master workbook in a folder then put another folder with your other 3 workbooks as a sub folder in the folder and that would be the name of the folder in red below. Another alternative to this is to actually open each file and copy and paste the data between workbooks, which is slower but will not be too slow if your only importing data from 3 workbooks at a time, but the benefit of opening the workbook and copy/pasting is you can copy and paste info based on conditions you set, where as importing with a formula you cannot. Ron de Bruin has a great website covering this. Hope this helps.

http://www.rondebruin.nl/copy3.htm

Code:
Sub Import()
    Dim MyPath As String, Location As String, FilesInPath As String
    Dim Myfolder As String, MyFiles() As String
    Dim Fnum As Long, CalcMode As Long, k As Long
    Dim MainDBwb As Workbook
            Location = ThisWorkbook.Path
            Myfolder = "\YOURFOLDERSNAME\"
 
            'Fill in the path\folder where Flight Logs are
            MyPath = Location & Myfolder 
 
            'If there are no Excel files in the folder exit the sub
            FilesInPath = Dir(MyPath & "*.xls*")
            If FilesInPath = "" Then
                Exit Sub
            End If
 
            'Fill the array(myFiles)with the list of Excel files in the folder
            Fnum = 0
            Do While FilesInPath <> ""
                Fnum = Fnum + 1
                ReDim Preserve MyFiles(1 To Fnum)
                MyFiles(Fnum) = FilesInPath
                FilesInPath = Dir()
            Loop
 
            'Loop through all files in the array(myFiles)
            If Fnum > 0 Then
                For Fnum = LBound(MyFiles) To UBound(MyFiles)               
                    For k = 11 To 29 Step 2
                        Set MyRange = Range(ThisWorkbook.Sheets(1).Range("C10000").End(xlUp).Offset(1, 0), ThisWorkbook.Sheets(1).Range("C10000").End(xlUp).Offset(1, 17))
                        MyRange = "=if('" & MyPath & "[" & MyFiles(Fnum) & "]" & YOURSHEETNAME & "'!B" & k & "="""",""""," & "'" & MyPath & "[" & MyFiles(Fnum) & "]" & YOURSHEETNAME & "'!B" & k & ")"
                        MyRange.Value = MyRange.Value
                    Next k
 
                Next Fnum
            End If
 

Watch MrExcel Video

Forum statistics

Threads
1,127,872
Messages
5,627,393
Members
416,245
Latest member
Xterminat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top