What is the VBA code to click on a Hyperlink, review the page and get back to the main sheet

[farvardin]

Hello,
I have a list with 3 columns:
Package Name:
Latest Date:
Link:

The VBA should click on the link, go to the linked sheet, capture a value and return to the main page.
I have a problem with writing a loop to go through the link in each row.
Can you please help:

Package	Date	Link
kolabi Pack	1-Jan-19	VIC
Anjan Pack	12-Aug-18	TAS
Coala Pack	4-Dec-18	WA

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Code:

Sub Lop
Sheet1.Activate

count1 = Range("c2", Range("c1").End(xlDown)).Cells.count
y = 2
Do Until y = count1
Sheet1.Activate

Range("C" & y).Hyperlinks(Range("C" & y).Value).Follow NewWindow:=False, addhistory:=True

y = y + 1
Loop

end sub

 

[My Aswer Is This]

Your wanting to retrieve some values from some sheets and return them some place.

Would you care to explain what your ultimate goal is.
A script should not need to activate hyperlinks and then do something

Where are the sheet names? Are they in column C?
And on the sheets where is the value you want returned?
And where do you want this value returned to?

 

[farvardin]

Hello,
Thanks for replying so quickly.
My ultimate goal is to capture the data from external link: "ProjectWise".
However, I am testing the options that VBA has for Hyperlinks.

TO answer you Qs:
Where are the sheet names? Are they in column C? Yes.
And on the sheets where is the value you want returned? Always on A1 & A2. Should be a fixed location
And where do you want this value returned to? Return them to Col D & E in the main sheet. or store them in Array.


Below is the latest code I wrote for testing and it is working fine:

Sub hyper()




Dim count1 As Long, t As Long
Dim y As Long
Sheet1.Activate
count1 = Range("c2", Range("c1").End(xlDown)).Cells.count


y = 2
Do Until y = count1 + 2
Sheet1.Activate


Range("C" & y).Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Range("A2").Copy Sheets("Sheet1").Range("C" & y).Offset(0, 1)
Range("B2").Copy Sheets("Sheet1").Range("C" & y).Offset(0, 2)
y = y + 1
Loop


Sheet1.Activate
MsgBox ("Extraction of " & count1 & " Links Completed")



End Sub