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

snowcrash

New Member
Joined
Mar 1, 2018
Messages
10
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

New Member
Joined
Mar 1, 2018
Messages
10
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
 

Forum statistics

Threads
1,081,420
Messages
5,358,574
Members
400,504
Latest member
RedSquirrel

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top