Excel VBA Hyperlink to Variable name worksheet

[ricksmith324]

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

 

[Norie]

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?

ActiveCell.FormulaR1C1 = "=' '!R[-3]C[1]"
Range("B4").Select
ActiveCell.FormulaR1C1 = "=' '!R[-2]C"
Range("A4").Select

 

[ricksmith324]

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.