Need Help--Trying to get Excel workbooks to condense onto one master workbook

[Ndomer14]

Hi,

So I'm sure this has been done over and over again, but I still can't figure it out.

I using office 2007 and trying to copy Summary tables of projects from a common portal into a master workbook. I would like all the Summary infomation to have its own individual tab and be named according to the project in the master workbook, but also create a summary tab of all the summaries in the master workbook once all the info is inputed. Also, How do I get the information to update on the master workbook if someone makes a change to one of the individual workbooks that I am pulling the data from? Updates will mostly likely happen on a monthly basis.

All of the summary tables are the same. They take up cells A1:P20 in all the individual workbooks.

Please let me know if anyone can help! I really appreciate it!!!

[Tinbendr]

Here's an article about summary workbooks.

Also check out Shared workbooks. There are several very good videos on Youtube describing this built in feature of Excel.

You're request is much too broad for anyone to offer specific advise. Aand without a Sample workbook to work on, noone is likely to be willing to help. (You can attach a sample by clicking Go Advanced, scroll down until you see Manage Attachments. Be sure to include specific examples about what you have and what you expect.)

[Ndomer14]

Thanks for the advice! This is the code I am working with currently, but I still am having issues on how to locate the different excel documents from the SharePoint site. It is a URL and not a local disk because multiple people need access. I created a list that has all of the URLs I need, but I do not know how to loop through it to open all the files and extract the information I need.

Specifically, the LookIn function what should I put there if I have multiple folders to look in on the website?

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
'SharePoint site, there are 12 different folders containing the excel workbooks that I need to access that have different names
.LookIn = "http:"
.FileType = msoFileTypeExcelWorkbooks
'Optional filter with wildcard
'.Filename = "Book*.xls"
If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

'copy table in range A1:P20 and paste the copied table to a new worksheet in the workbook

wbResults.Close SaveChanges:=False
Next lCount
End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Thanks