Excel VBA Hyperlink to Variable name worksheet

ricksmith324

New Member
Joined
Mar 21, 2017
Messages
4
Hi All,

Relatively new to VBA so still trying to get the hang of it. Nonetheless, I'm looking to build in a hyperlink function into my macro. This macro is taking a template, copying it to a new sheet, and then adding a row to a summary sheet that includes information from this newly created sheet. The newly created sheet contains employee information specific to one employee.

Everything is working just fine except the hyperlink. Essentially the summary sheet contains multiple employee information rolled up at a summary level, and what I would like is for the employees name to be a hyperlink that will bring them to the employee's tab. It creates the hyperlink but keeps giving me a "Reference is not valid" message. Below is the code that I am using (hyperlink code is in red):

Any help would be very much appreciated! '
Sheets("Employee_Temp").Select
Sheets("Employee_Temp").Copy Before:=Sheets(4)
Range("B1").Select
ActiveCell.FormulaR1C1 = " "
Range("C2").Select
Sheets("Department Summary").Select
ActiveWindow.SmallScroll Down:=-12
Rows("4:4").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A4:J4").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("A4").Select
ActiveCell.FormulaR1C1 = "=' '!R[-3]C[1]"
Range("B4").Select
ActiveCell.FormulaR1C1 = "=' '!R[-2]C"
Range("A4").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"' '!A1"
Range("C4").Select
ActiveCell.FormulaR1C1 = "=MAX(' '!R[1]C[-1]:R[146]C[-1])"
Range("C4").Select
ActiveCell.FormulaR1C1 = "=MAX(' '!R5C2:R150C2)"
Range("D4").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-1],' '!R[1]C[-2]:R[146]C[2],5,0),"""")"
Range("D4").Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],' '!R5C2:R150C6,5,0),"""")"
Range("E4").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-2],' '!R[1]C[-3]:R[146]C[2],6,0),"""")"
Range("E4").Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2],' '!R5C2:R150C7,6,0),"""")"
Range("F4").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-3],' '!R[1]C[-4]:R[146]C[3],8,0),"""")"
Range("F4").Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-3],' '!R5C2:R150C9,8,0),"""")"
Range("G4").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-4],' '!R[1]C[-5]:R[146]C[3],9,0),"""")"
Range("G4").Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-4],' '!R5C2:R150C10,9,0),"""")"
Range("H4").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-5],' '!R[1]C[-6]:R[146]C[3],10,0),"""")"
Range("H4").Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-5],' '!R5C2:R150C11,10,0),"""")"
Range("I4").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-6],' '!R[1]C[-7]:R[146]C[4],12,0),"""")"
Range("G8").Select
Sheets(" ").Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=-15
Range("B1").Select
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Are you getting the error when you click the hyperlink or when you run the code?

By the way, why are there no sheet names in the formulas in this part of the code?
Code:
ActiveCell.FormulaR1C1 = "=' '!R[-3]C[1]"
Range("B4").Select
ActiveCell.FormulaR1C1 = "=' '!R[-2]C"
Range("A4").Select
 
Last edited:
Upvote 0
I'm getting the error when I click on the hyperlink.

I'm not entirely sure of the no sheet name as most of the macro I ran through the macro builder. But in order to get the data from the copied template over to the summary tab, I had to enter a space into the tab name. So essentially the copied template has a "blank" tab name.

What I may have failed to mention is that there is an additional macro that runs in the background of this one. After the template is copied and created, the user inputs an employees name into a cell (B1) on the template. The tab then automatically renames to whatever is typed into cell B1.
 
Upvote 0

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top