VBA: Opening multiple hyperlinks to excels and copying data to a consolidation excel file

[snowcrash]

Need
I have a file where in column “C” I have a list of different hyperlinks to excel workbooks stored on SharePoint (number of rows and links is different every month, but approx. 140). I want to retrieve information from these hyperlinks (the information needed is all in one range on a hidden sheet in the excel file) and paste into my central consolidation file on the same row as the hyperlink.

I have managed to write a simple script to retrieve the information from one file, however cheating as I name the file to use once the hyperlink has been opened on my test file.
I have applied screen refresh and display alerts to false, and once I have got the information from the file I don’t want to save once closed

I really need help to re-write this and understand how to loop this for undefined number of rows and hyperlinks

Code i currently have


Sub Consolidation()


Application.ScreenUpdating = False
Application.DisplayAlerts = False

Windows("centralConsolidation.xlsm").Activate
Range("C4").Hyperlinks(1).Follow


Windows("excelname.xlsm").Visible = True
Sheets("hiddensheet").Visible = True
Sheets("hiddensheet").Select
Range("B4:ADZ4").Select
Selection.Copy
Windows("centralConsolidation.xlsm").Activate
Range("F4").Select
ActiveSheet.Paste
Windows("excelname.xlsm").Activate
Sheets("hiddensheet").Select
ActiveWindow.SelectedSheets.Visible = False
ActiveWindow.Close savechanges:=False

Windows("centralConsolidation.xlsm").Activate
Range("B2").Select

Application.DisplayAlerts = False
Application.ScreenUpdating = True

End Sub

 

[snowcrash]

Hi All,

I keep trying to work out this out, but not gaining any ground

Is anyone able to help point me in the right direction to loop the above macro?

/Snowcrash