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
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