Thread: hyperlinks

Can you elaborate on the below ?

If not, is there a way to find the cell address within a worksheet, once a hyperlink is found on that worksheet?

I follow (no pun intended) the idea of using the hyperlink to ascertain the range to be copied etc but what I don't follow is on what basis the hyperlink itself is being used to invoke the routine.

I'm probably being dense but it's hard to apply the narrative above to the file you uploaded (I appreciate you can't upload the real file).

Perhaps if you outline the workflow in terms of the sample file that may help... ie user does x, invokes y which in turn invokes z or something along those lines.

--------------------------------------------------------------------------------
I'm probably being dense but it's hard to apply the narrative above to the file you uploaded (I appreciate you can't upload the real file).

Perhaps if you outline the workflow in terms of the sample file that may help... ie user does x, invokes y which in turn invokes z or something along those lines.

Hey man,

I appreciate your help, I guess my excel sheet I've created isnt ideal in representing what Im trying to achieve.

I've attached hopefully a much better, clearer sheet....this might make it clearer what Im trying to do.

Basically the way that the sheet works currently is:

1)user looks at the requirement.
2)user wants to find out more about this requirement so in order to do this, clicks on the "VAM" hyperlink to find out how to pass the requirement.
3)Hyperlink then redirects the user to the VAM sheet where the information is listed.

My objective with this sheet is to:
1) Run a macro.
2) This macro will then scan across the Compartment Details sheet.
3) Find a cell which contains a hyperlink
4) Create a new worksheet
5) Copy the information that the hyperlink points to, onto the new worksheet in the first coloumn it see's empty.

With this new worksheet I intent to import the information into my other system which extracts the data so that I can stick it into a report - but I guess you wont be concerned with this part of my problem.

Does that make it any clearer about what I'm trying to achieve?

OK so just, finally, to be clear, VAM Pt2 is meant to represent your preferred output ?

and by:

Create a new worksheet

and

Copy the information that the hyperlink points to, onto the new worksheet in the first coloumn it see's empty

I am presuming you mean: copy all hyperlink data to one sheet (rather than one sheet per link) and copy said data to first empty row (rather than column)

Is all of the above correct ?

Apologies for delay - was not around.

The below is I confess a guesstimate re: requirements...

Code:

Public Sub XAmple()
Dim wsCD As Worksheet, wsNew As Worksheet
Dim HL As Hyperlink
Dim rngCopy As Range
On Error GoTo ExitPoint
Application.ScreenUpdating = False
Set wsCD = Sheets("Compartment Details")
Set wsNew = Sheets.Add(After:=Sheets(Sheets.Count))
For Each HL In wsCD.Hyperlinks
    wsCD.Activate: HL.Follow
    Set rngCopy = ActiveCell.Offset(, 1 - ActiveCell.Column).Resize(, 20)
    wsNew.Cells(wsNew.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 20).Value = rngCopy.Value
    Set rngCopy = Nothing
Next HL
ExitPoint:
Set wsCD = Nothing
Set wsNew = Nothing
Application.ScreenUpdating = True
End Sub

The above when run would create a new sheet with the details (A:T) of each hyperlinked cell, you will get duplicates of course where the links are the same.

If needed you could keep track of ranges processed and exclude repeats.

Originally Posted by therealjag

If I were to now try and copy that data onto a new workbook instead of worksheet, how would I go about doing this?

And I am intrigued by your suggestion about keeping a list of the ranges processed, how would this be possible?

Pretty ugly code but the below would for ease compile the data onto one sheet within main file before copying to a new file, saving & closing new file (same location as parent file but with added extension of _LIST_datetimestamp), deleting the created sheet from the main file and reverting back to original sheet.

Code:

Public Sub XAmple()
Dim wsCD As Worksheet, wsNew As Worksheet, wsCurrent As Worksheet
Dim HL As Hyperlink
Dim rngCopy As Range, rngHL As Range
Dim boolAdd As Boolean
On Error GoTo ExitPoint
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
End With
Set wsCurrent = ActiveSheet
Set wsCD = Sheets("Compartment Details")
Set wsNew = Sheets.Add(After:=Sheets(Sheets.Count))
For Each HL In wsCD.Hyperlinks
    wsCD.Activate: HL.Follow
    If rngHL Is Nothing Then
        boolAdd = True
        Set rngHL = ActiveCell
    Else
        boolAdd = Intersect(ActiveCell, rngHL) Is Nothing
        Set rngHL = Union(rngHL, ActiveCell)
    End If
    If boolAdd Then
        Set rngCopy = ActiveCell.Offset(, 1 - ActiveCell.Column).Resize(, 20)
        wsNew.Cells(wsNew.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 20).Value = rngCopy.Value
        Set rngCopy = Nothing
    End If
Next HL
wsNew.Copy
With ActiveWorkbook
    .SaveAs Replace(ThisWorkbook.FullName, ".xls", "_List_" & Format(Now, "yyyymmddhhmmss") & ".xlsx")
    .Close
End With
wsNew.Delete
wsCurrent.Select
ExitPoint:
Set rngHL = Nothing
Set wsCD = Nothing
Set wsNew = Nothing
Set wsCurrent = Nothing
With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
End With
End Sub