Hi,

I'm trying to pull data from various excel spreadsheets into one summary report in a grid.

Each spreadsheet is held within a folder, named as a date such as 2008-10-14, 2008-10-21 etc. I have so managed to write some VBA so that when a date is selected at the top of my summary report, the date of the corresponding folder is inserted into a link which points to one report within the folder and pulls back one piece of data.

Here is my code so far:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim firstpart As String, secondpart As String, solutions_design_overall_status As String
firstpart = "'http://sharepointsite.net/Meetings/"
secondpart = "/[Vauxhall Report.xls]Worksheet1'!D11"
If Not Intersect(Target, Range("C6")) Is Nothing Then
Application.EnableEvents = False
    'Cells.Hyperlinks.Add anchor:=Range("V11"), Address:=firstpart & Format(Target + 4, "yyyy-mm-dd") & secondpart, TextToDisplay:=firstpart & Format(Target + 4, "yyyy-mm-dd") & secondpart
    vauxhall_overall_status = "=" & firstpart & Format(Target + 4, "yyyy-mm-dd") & secondpart
    Range("B2").Value = vauxhall_overall_status
Application.EnableEvents = True
End If
End Sub
At the moment all this VBA code is doing is taking the date from the summary report, creating a link which includes the correspending date for the folder to look in, and pulls the 'overall status' data from the Vauxhall report.

How do I write a loop so that once a date is selected from the summary report, it looks within the corresponding folder on the sharepoint site, as it is doing at the moment, and then pulls data from the Vauxhall report, Ford report, Fiat report etc. for each of the headings along the top of the grid (overall status, teamwork, communication, training)????

Many Thanks,
Alex