Creating Macro to open Link to excel sheet - copy data from specified cell and then paste into original sheet

[SJTGM]

Hi All,

I have been trying to create a Macro to copy data from a hyperlinked excel sheet, but have not had much luck.

Basically I have a master excel workbook with columns for different data. This data is populated manually from other excel workbooks which are of a standardised format and the data is always in the same cells. i receive multiple of these standardised workbooks every day and would like to automate the process of opening the file and copying the data into the master sheet. The data is always in the same Cells and i would be linking the standardised sheets from a local drive in the first Cell of each row.

E.g.
Header: Width Length Height
Data: 12cm 67cm 34cm

I am trying to create a Macro that, when run, clicks the link in the first cell (e.g. A1) to go to one of the standardised excel documents.

Once in this document, i want to copy the data from the specific cells and paste into the master sheet in the relevant columns.

I would like this to work relatively, so i can put a link in for each new standardised sheet i receive and run the macro to populate the adjacent columns.

Any assistance would be greatly appreciated

Thank you

 

[NMa91]

Sub CopyData()
Dim Lnk As Range
Set Lnk = Sheets("MasterSheet").Range("A1")
Do Until Lnk.Value = ""
    Lnk.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    'Do your stuff to pick data from here
    Set Lnk = Lnk.Offset(1, 0)
Loop
End Sub