[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Copy Names and add hyperlink (Excel VBA)

Posted on 2012-08-11
15
Medium Priority
?
397 Views
Last Modified: 2012-09-21
Hello,

I have a spreadsheet on a worksheet called 'Data'. There are names in column 'F' of the spreadsheet.

I would like to copy each *unique* name (there are many duplicates) that appears in column 'F' on the 'Data' worksheet to a worksheet called 'Summary' starting the list of names from Cell 'B13' downwards on the 'Summary' worksheet.

As this happens I would like to make the name copied a hyperlink on the Summary page so when finished you can click on the name and it will take you to the worksheet with the name of that person.

(The worksheet has tabs already created for each person that will be copied across).

Hope this makes sense, please ask if I have not bee clear ;)

I would like to do this in Excel as a VBA module.
0
Comment
Question by:gisvpn
  • 10
  • 3
  • 2
15 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 38283959
If you would provide a sample workbook, it would help expedite your solution.

Dvae
0
 
LVL 24

Expert Comment

by:Steve
ID: 38284075
It would likely be simpler to create the list based upon the sheets present.
Or would you like missing sheets to be created in not present in the workbook?
0
 
LVL 24

Expert Comment

by:Steve
ID: 38284101
The attached workbook has two main code parts:

1) Added to the sheet with the "links"
Will set column A to open the sheet with matching Sheet.Name
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A:A")) Is Nothing Then Sheets(Target.Value).Select
End Sub

Open in new window

2) Added to a code module to extract sheet names to sheet1
Sub sheetsMake()
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")
x = 2
For Each sht In wb.Sheets
    ws.Cells(x, 1) = sht.Name
    x = x + 1
Next
End Sub

Open in new window


See attached workbook example..
Code can be changed to your specifics, though this should get you started.
Sheet-selection.xlsm
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Expert Comment

by:Steve
ID: 38284107
The following code adds hyperlinks for each sheet:

Sub sheetsMake()
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Sheets("Summary")
x = 13
For Each sht In wb.Sheets
    ws.Hyperlinks.Add Anchor:=ws.Cells(x, 2), Address:="", SubAddress:=sht.Name & "!A1", TextToDisplay:=sht.Name
    x = x + 1
Next
End Sub

Open in new window

Sheet-selection.xlsm
0
 

Author Comment

by:gisvpn
ID: 38284122
Hello,

Attached is a workbook example of what I was trying to achieve.

On the Summary worksheet you have the list of names which is just a list of unique names from column F on the data worksheet.

I would like the code to check column F each time it runs and should update the list on the summary workbook each time it runs. I would like to names to be hyperlinks to their relevant tabs.

Dont worry about the tabs the persons tab will always be there.


hope this helps - if you add the code into the example workbook and post back that would help me greatly.
example-workbook.xlsx
0
 
LVL 24

Expert Comment

by:Steve
ID: 38284134
And finally, this one has a userform which fills with all the sheetnames to allow users to pick from a list.
Sheet-selection.xlsm
0
 
LVL 24

Expert Comment

by:Steve
ID: 38284156
The attached workbook is based upon Sheets (as you say all names will have a sheet)
example-workbook.xlsm
0
 
LVL 24

Expert Comment

by:Steve
ID: 38284172
Here is the code and attached workbook for the data name method:

Sub DataNamesMake()
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Sheets("Summary")
Set ws2 = wb.Sheets("data")
x = 13
y = 1

Do While Len(ws2.Cells(y, 6)) <> 0
If WorksheetFunction.CountIf(ws.Range("B:B"), ws2.Cells(y, 6).Value) = 0 Then
    ws.Hyperlinks.Add Anchor:=ws.Cells(x, 2), Address:="", SubAddress:=ws2.Cells(y, 6).Value & "!A1", TextToDisplay:=ws2.Cells(y, 6).Value
    x = x + 1
End If
y = y + 1
Loop
End Sub

Open in new window

example-workbook.xlsm
0
 
LVL 24

Expert Comment

by:Steve
ID: 38284178
The attached workbook will only add NEW names from the data list (leaving existing ones where they are)

Sub DataNamesMake()
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Sheets("Summary")
Set ws2 = wb.Sheets("data")
x = ws.Cells(Rows.Count, 2).End(xlUp).Row + 1
y = 1

Do While Len(ws2.Cells(y, 6)) <> 0
If WorksheetFunction.CountIf(ws.Range("B:B"), ws2.Cells(y, 6).Value) = 0 Then
    ws.Hyperlinks.Add Anchor:=ws.Cells(x, 2), Address:="", SubAddress:=ws2.Cells(y, 6).Value & "!A1", TextToDisplay:=ws2.Cells(y, 6).Value
    x = x + 1
End If
y = y + 1
Loop
End Sub

Open in new window

example-workbook.xlsm
0
 
LVL 42

Expert Comment

by:dlmille
ID: 38284508
That's quite a few posts.
0
 
LVL 24

Expert Comment

by:Steve
ID: 38284909
Yep, hopefully one of them is good :)

I am just worried that:
Either
There are sheets which are not in the list.
or
There are names in the list without sheets.

Whether the list of hyperlinks should be fresh each time or should add on each time.
Only gisvpn can know for sure, so have tried to give a good few variations.
0
 

Author Comment

by:gisvpn
ID: 38285553
Hi The_Barman

Thanks for the posts there are some good suggestions there. I had a question.

I am using the code below with a few minor changes - however all works perfects but the hyperlink gives an error message when you click on the hyperlink (as attached).

I have double checked the sheet does exist for the name however it gives this error - any ideas.






im wb1 As Workbook
Dim ws1 As Worksheet

Set wb1 = ThisWorkbook
Set ws1 = wb1.Sheets("Summary")
Set ws2 = wb1.Sheets("data")

ws1.Rows("15:1000").Delete
x = 15
y = 1

Do While Len(ws2.Cells(y, 6)) <> 0
If WorksheetFunction.CountIf(ws1.Range("B:B"), ws2.Cells(y, 6).Value) = 0 Then
    ws1.Hyperlinks.Add Anchor:=ws1.Cells(x, 2), Address:="", SubAddress:=ws2.Cells(y, 6).Value & "!A1", TextToDisplay:=ws2.Cells(y, 6).Value
    ws1.Cells(x, 4) = WorksheetFunction.SumIf(ws2.Range("F:F"), ws1.Cells(x, 2), ws2.Range("J:J"))
    x = x + 1
End If
y = y + 1
Loop



On another note to your questions - a fresh list is great to have each time and the ws1.Rows("15:1000").Delete is perfect for this.

As this code does not create the tabs which would at this point already exist - can you add to the code a flag that if a link and name is created but there isnt a sheet it creates the link but makes the text red and if there is a sheet that exists but a name is not on the list it puts the sheet name at the end of the list and makes the text blue?
Capture.PNG
0
 
LVL 24

Expert Comment

by:Steve
ID: 38285712
I cannot say why the hyperlink is not working, it may be case sensitive, could you post example workbook for testing.

As for creating new sheets if the name is missing, I will work on that for you asap.
0
 

Author Comment

by:gisvpn
ID: 38285750
Hi The Barman,

Thanks that sounds great - could I ask what the error message I get does suggest - it currently has a reference attached to the hyperlink that does not exist?
0
 
LVL 24

Accepted Solution

by:
Steve earned 2000 total points
ID: 38286632
Yes, the error message suggests that the sheet is not there or that there is something else wrong with the hyperlink.

In my first answer I produced a method that does what you like without the use of hyperlinks.
Would you prefer it to use this method (it can be a lot more reliable than Hyperlinks due to not needing paths and addresses too much) can be formatted to appear like Hyperlinks if you like.

Using Summary sheet code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("B13:B999")) Is Nothing Then Sheets(Target.Value).Select
End Sub 

Open in new window

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question