Linking cells from multiple Excel files and multiple spreasheets into a separate Excel file spreadsheet

[pleephd]

We created an Excel spreadsheet form for multiple hospitals to enter their data. As the end, we now have 17 separate Excel files that each contain 31 worksheets. I need to get data from reach of the 17 files. need to get the numbers from three cells (Numerator, denominator, & rate) from 26 of the 31 worksheets in each of the 17 files. For most of the 26 worksheets the three cells are in the same spot (24 of them). For one worksheet (in the 17 files), I will need to copy four sets of 3 cells and from one other worksheet, I only need to copy 1 cell. I would be satisfied if I could automate linking the cells in the same location (for 24 of the worksheets) and then doing the other two the long way,

I know that I could do this the long way by going into the Master file and then put = in a cell and then go into each file an then each worksheet and click into the cell I want, but that would take forever. I could also in the master file use code in the cell and do something like put = and then put the filename, worksheet name, and cell in each cell in the mater file, but this also would take too long.

I know this can be automated and have seen some videos and text of some things that are similar to what I need, but nothing that matches. Therefore, I am asking here with the hope someone can tell me how to do this or point me to the right resource.

Thank you.

 

[offthelip]

You haven't given details of the names of anything or where you want the results, so I made an assumption that in a sheet called "Filessheetslinks" you have a list of the full path to all the files in column A, a list of all the sheet names in column b ( assuming the names are the same for each file) and then a list of the 3 addresses you want to link in column C , format B3 , AD4 etc
This code compiles but I can't run it so it is untested:

Sub adlink()CurrentWorkbookName = ActiveWorkbook.Name
listfiles = Sheets("Filessheetslinks").Range("B1:B17").Value
listsheets = Sheets("Filessheetslinks").Range("C1:C26").Value
addlinks = Sheets("Filessheetslinks").Range("A1:A3").Value
CurrentWorkbookName = ActiveWorkbook.Name


For i = 1 To 17
      newf = listfiles(i, 1)
      Workbooks.Open Filename:=newf
      NewWorkbookName = ActiveWorkbook.Name
       For j = 1 To 26
       Windows(NewWorkbookName).Activate
       newsh = listsheets(j, 1)
       Worksheets(newsh).Select
        For jj = 1 To 3
        
            Windows(NewWorkbookName).Activate
            Worksheets(newsh).Select
            
            
            linkad = addlinks(jj, 1)
            Range(linkad).Select
            Selection.Copy
            
             Windows(CurrentWorkbookName).Activate
             Worksheets("sheet1").Select
             Cells(j * jj, i).Select
             ActiveSheet.Paste link:=True
        Next jj
      Next j
Next i


     
End Sub

Hopefully this will get you started