Using the value from a cell to rename tab and create hyperlink on summary page

[ileah14]

Hello,

This is my very first post so pardon any faux pas, plus I am only a beginner at teaching myself VBA so please excuse any knowledge gaps. I've been trying to Frankenstein codes from too many forums and just can't get what I want to do done.

My conundrum (and I will try to be as specific and detailed as possible; hopefully this isn't too specific an issue to get sorted on the forum)-

I download numerous worksheets and insert each into a summary workbook one at a time using unique numbers in a list of document tags. (i.e I search doc #0274055504 on our system and download the report). Each worksheet/report has a value in cell D11 which is it's unique identifier/document number/tag.
1. I take the value from D11 (i.e 0274055504) and rename the tab as this tag:
(I have this section working but am including it in case any of the elements will follow into other sections)

Dim xWs As Worksheet
Dim xRngAddress As String
Dim xName As String
Range("D11").Select
xRngAddress = Application.ActiveCell.Address
For Each xWs In Application.ActiveWorkbook.Sheets
xName = xWs.Range(xRngAddress).Value
If xName <> "" Then
xWs.Name = xName
End If
Next


2. I now want to find the cell containing this tag (specifically the value from D11 in my download - i.e. 0274055504) my summary doc and make that cell a hyperlink to the tab in the workbook (which is named 0274055504). So that clicking the cell takes you to the tab of that name.
This is what I have and want to make it dynamic depending on the value in cell D11, basically:

Range("D11").Select

'in this example, as you can hopefully see, the value in D11 is 0274055504

Selection.Copy
Sheets("working").Select
Cells.Find(What:="0274055504", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'0274055504'!A1", TextToDisplay:="0274055504"


I want to replace all the numerical text '0274055504' with a reference to D11. Any and all suggestions appreciated, maybe there is a much simpler way that is completely different from the way I'm working it now...


Thanks so much.

PS I hope I did this right...

 

[My Aswer Is This]

It sounds like you want hyperlinks you can click on to take you to each one of the sheets in your workbook.

So for example if your sheet name is "George" you can click on a cell with the value "George" and you will be taken to the sheet named "George"

So on what sheet and in what column do you want all these Hyperlinks put?

For example we could put all these links into a sheet named "Master" column "A" and if you clicked on the cell you would be taken to that sheet.

 

[ileah14]

Thanks for your help!
I already have a column, my "Master" is the 'working' tab, with the numbers listed in a column. There are other details for each report in the working tab as well as the column of doc codes.
So I want the VBA to find the number in the worksheet in order to create the hyperlink in that cell. The cell in the working tab will usually be the last cell selected, which is/is near the cell that contains the entry I need, so the find function isn't essential...
Does that make sense and answer you questions?

 

[My Aswer Is This]

If you have all these numbers. In a column on your sheet named "Master"
I do not need to look in a cell on each sheet.
All I need to know is in what column on the Sheet named "Master" are these numbers.
If you have:
"12387" in Range("A1") of sheet named “Master” I can put a link in that cell to take you to a sheet named "12387"
If you have the number "478124" in sheet named "Master" Range ("A2") I can put a link in Range("A2") which will take you to a sheet named "478124"
Etc. etc.

 

[ileah14]

The values are in tab named "working" in column K

 

[My Aswer Is This]

Assuming your sheet is named "working"
And all the values in column "K" of sheet named "working" are sheet names.
Try this:
Make sure your sheet name is "working" not "Working"
Names like this must be perfect.

Sub AddHyperLinks()
Application.ScreenUpdating = False
Dim C As Range
With Sheets("working")
    For Each C In .Range("K1:K" & .Range("K" & .Rows.Count).End(xlUp).Row)
        .Hyperlinks.Add Anchor:=C, Address:="", SubAddress:="'" & C.Value & "'!A1"
    Next C
End With
Application.ScreenUpdating = True
End Sub

 

[ileah14]

That Worked perfectly. Thank you!

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

That Worked perfectly. Thank you!

 

[Jmoz092]

Sub AddHyperLinks()
Application.ScreenUpdating = False
Dim C As Range
With Sheets("working")
    For Each C In .Range("K1:K" & .Range("K" & .Rows.Count).End(xlUp).Row)
        .Hyperlinks.Add Anchor:=C, Address:="", SubAddress:="'" & C.Value & "'!A1"
    Next C
End With
Application.ScreenUpdating = True
End Sub

I just wanted to say thanks for this, it helped me with part of a project I'm working on.

 

[My Aswer Is This]

Glad I was able to help you.

I just wanted to say thanks for this, it helped me with part of a project I'm working on.