Using one sheet to collate data from multiple workbooks

I THINK i've been naughty, as I have just put this into the FORMULA thread, BUT I think that this could end up having MACRO in this?????

As the title says - I have one sheet which is used daily, but this will have data in certain cells which will be pulled from different work books. Reason for this, some idiot in work thinks its a great idea to the same job twice, but I don't like the idea of spending half an hour to an hour finding all this data. So I would like to do this automatically!

BUT...... (This is not as simple as putting a formula link into a cell)

I have different variables which will determine where and what files to look in!
This may even have to be macro rather than formulas????

Say for Arguments sake my main sheet which will collect all the data will be called "Data collection" And the current WK number is 50

1. Some files will ALWAYS be in example: C:Documents\work\Audit ......... But the sheet name will change weekly. If Its week 50, it will look on TAB "WK50" and this week number will be either: Be a WEEK NUM formula in A2 or I will write WK50.

In this sheet I will have to then look up a figure at the bottom of the sheet, depending on the day I will have to lookup a certain cell, I have a macro on this sheet that opens up a certain page on a certain day/time, so I might be able to modify this to do this part?????


2. Some links will ALWAYS be in the same cell, but the file/workbook name will vary by week. E.G if the week is week 50, then the file will be called WK50. Again this figure will be in A2 on the collating sheet. So this link would kind of look like C:\Documents\Work\Waste\"THEN THE VALUE IN A2" as the folder will be called WK50

3. Some links will ALWAYS be the same file name, but the TAB in this sheet will be a shift name and a date. E.G "3 DAYS 19.12.2012" these two name parts will be also written on this sheet as a reference. "3Days" would be in A3 and the date will be in A4.

4. Exactly as above but, depending on the DAY will look up a certain cell range. E.G Friday will open the above sheet, and look up E1 Saturday will look up E2 Sunday E3

And to top all this off!!!! When I have tried doing links to other workbooks, The collating sheet needs to have the VALUES pasted in! as I have had the actual formula pasted in last time I tried and obviously them figure don't exist in the current workbook.

This also shouldn't make a difference but, it's on a network, so the C: I have been using as an example, won't be a drive name, it will start off as something like CALSQ, which shouldn't cause a problem????

Any one of these answers will help a great deal, I can link basic things up, but this has alwyas been live data, but this is totally different which is now new to me!

Thanks.

I've found this code below which I will need to adapt fro part of my sheet, but this code copies a set amount of rows and columns.
How would I go about changing the rows and columns to designated ranges, Say copy from closed workbook A1 and paste into current workbook A2.

I will have to obviously change file locations as looking at this, my workbook will not be in the same location as the one i'm copying from which isn't a problem

Option Explicit 
 
 'you can extract data from a closed file by using an
 'XLM macro. Credit for this technique goes to John
 'Walkenback > http://j-walk.com/ss/excel/tips/tip82.htm
 
Sub GetDataDemo() 
     
    Dim FilePath$, Row&, Column&, Address$ 
     
     'change constants & FilePath below to suit
     '***************************************
    Const FileName$ = "Book1.xls" 
    Const SheetName$ = "Sheet1" 
    Const NumRows& = 10 
    Const NumColumns& = 10 
    FilePath = ActiveWorkbook.Path & "\" 
     '***************************************
     
    DoEvents 
    Application.ScreenUpdating = False 
    If Dir(FilePath & FileName) = Empty Then 
        MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist" 
        Exit Sub 
    End If 
    For Row = 1 To NumRows 
        For Column = 1 To NumColumns 
            Address = Cells(Row, Column).Address 
            Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address) 
            Columns.AutoFit 
        Next Column 
    Next Row 
    ActiveWindow.DisplayZeros = False 
End Sub 
 
 
Private Function GetData(Path, File, Sheet, Address) 
    Dim Data$ 
    Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _ 
    Range(Address).Range("A1").Address(, , xlR1C1) 
    GetData = ExecuteExcel4Macro(Data) 
End Function

Hi

I can make up a sample sheet and comment what I would like each one to do??
And the actual worksheet will be used everyday, preferably a macro button which will be used to update links.

Just need about an hour to make up a skeleton sheet.

Here are the sheets which would mirror the needs which I am struggling with.
Open the collection sheet, and the comments box will describe what is needed.







Audits.xlsCollection Sheet.xlsStaffing Sheet.xlsWK51.xls