Pulling Data From ~300 Individual Sheets Onto a Master Sheet

[jkondrat14]

Any all all help is appreciated!

I’m operating out of a company SharePoint, as multiple people need to be able to access these sheets. I want to have a Master sheet, that tracks information from ~300 individual site portfolios. I want the Master sheet to be able to automatically pull about 30+ data points off each portfolio and drop them into a single row, in a specific order. One row for one portfolio.

In my head, this works by me pasting a hyperlink (or something equivalent) in Column A, and all the needed data points automatically populating in the same row. This link serves two purposes. One, to provide a source for the Master to pull data points. Two, easy access to each site portfolio from the Master. Here’s a look at what I’ve tried so far:

VLOOKUP with HYPERLINK: This one I’ve had a little bit of success with, but I’m running into a logistical issue. I can get this to work in the sense that data points pulled get from the linked portfolio in Column A and get dropped into the same row. The problem is the data points only update when I “Open in Desktop”. If I edit a data point in a site portfolio while in Sharepoint, nothing gets updated on the Master. This won’t work for me, as most of the time my team will be editing portfolio sheets in Sharepoint.

Power Query: This one has promise, but I’m unfamiliar with PQ. Biggest hurdle I’m running into here is getting all wanted data points into a specific order in a single row. Each individual site portfolio has a ton of information, much more than what I want displayed on the Master sheet. With how much information in on each site portfolio, the data I want to pull is “all over the place”. Any advice on how to consolidate dispersed data into a single row using power query would be excellent.

Using VBA: This is by far the option that I have the least amount of experience in and would like to avoid if possible. I’ve never been skilled in any sort of coding or messing with macros. However, I’m always willing to try and learn, so if this is the best option, please let me know what I can try.

I appreciate the help from everyone. Please let me know if there are any questions or anything that I need to clarify. Thank you!

 

[shamimarita]

Hello Jkondrat14,

Here’s a comprehensive guide to consolidating data from 300 individual sheets into a Master sheet in Excel:

Option 1: Power Query (No Coding Needed)

• Load Data from SharePoint:
  • Go to Data > Get Data > From File > From SharePoint Folder.
  • Enter the SharePoint folder URL and load the file containing sheets.
• Combine Sheets:
  • Power Query will list all sheets. Select the sheets to combine.
  • Use the "Transform Data" option to structure data as needed.
• Load to Master Sheet: After cleanup, load the combined data to your Master sheet.

Option 2: VBA Automation

• List SharePoint links (or local file paths) in Sheet 1 Column A.
• Open the Visual Basic Editor (Alt + F11).
• Paste this VBA code in a new module:

Sub ConsolidateData()
    Dim wsMaster As Worksheet
    Dim ws As Worksheet
    Dim shLinks As Worksheet
    Dim wb As Workbook
    Dim lastRow As Long
    Dim dataRow As Long
    Dim filePath As String
    
    Set wsMaster = ThisWorkbook.Sheets("Master")
    Set shLinks = ThisWorkbook.Sheets("Sheet1") 'Where links are stored
    dataRow = 2 'Starting row in the Master sheet
    
    lastRow = shLinks.Cells(shLinks.Rows.Count, 1).End(xlUp).Row
    
    For i = 1 To lastRow
        filePath = shLinks.Cells(i, 1).Value
        Set wb = Workbooks.Open(filePath)
        
        'Assume data is in "Sheet1" and starts from A1
        Set ws = wb.Sheets("Sheet1")
        ws.UsedRange.Copy Destination:=wsMaster.Cells(dataRow, 1)
        
        dataRow = wsMaster.Cells(wsMaster.Rows.Count, 1).End(xlUp).Row + 1
        wb.Close SaveChanges:=False
    Next i
    
    MsgBox "Data consolidated successfully!"
End Sub

• Save the workbook as a macro-enabled file (.xlsm).
• Run the macro to fetch and consolidate data.

Option 3: Use SharePoint Lists

• Export each sheet to a SharePoint list.
• Use Data > Get Data > From Online Services > SharePoint List to sync data to Excel.