| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink
I have ten sheets and a summary sheet.
In the column A of summary sheet i have all the other sheets names. i need a code which will automatically link all the sheets as hyperlink to the summary sheet. Any help would be appreciatd. thx! Boss |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hyperlink
Try the below macro in summary sheet..
Sub Macro() Dim lngRow As Long, lngLastRow As Long lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow ActiveSheet.Hyperlinks.Add Range("A" & lngRow), Range("A" & lngRow) Next End Sub If this post helps click Yes --------------- Jacob Skaria "Boss" wrote: I have ten sheets and a summary sheet. In the column A of summary sheet i have all the other sheets names. i need a code which will automatically link all the sheets as hyperlink to the summary sheet. Any help would be appreciatd. thx! Boss |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hyperlink
Correction to the previous post Sub Macro1() Dim lngRow As Long, lngLastRow As Long Dim myRange As Range lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow Set myRange = Range("A" & lngRow) ActiveSheet.Hyperlinks.Add Anchor:=myRange, Address:="", SubAddress:= _ myRange.Text & "!A1", TextToDisplay:=myRange.Text Next End Sub If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below macro in summary sheet.. Sub Macro() Dim lngRow As Long, lngLastRow As Long lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow ActiveSheet.Hyperlinks.Add Range("A" & lngRow), Range("A" & lngRow) Next End Sub If this post helps click Yes --------------- Jacob Skaria "Boss" wrote: I have ten sheets and a summary sheet. In the column A of summary sheet i have all the other sheets names. i need a code which will automatically link all the sheets as hyperlink to the summary sheet. Any help would be appreciatd. thx! Boss |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hyperlink
This does not work...
I need to click on A1 and it should open the corresponding worksheet in the same file. thx! Example: A1.value = "test" then it should open the worsheet test thx! "Jacob Skaria" wrote: Try the below macro in summary sheet.. Sub Macro() Dim lngRow As Long, lngLastRow As Long lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow ActiveSheet.Hyperlinks.Add Range("A" & lngRow), Range("A" & lngRow) Next End Sub If this post helps click Yes --------------- Jacob Skaria "Boss" wrote: I have ten sheets and a summary sheet. In the column A of summary sheet i have all the other sheets names. i need a code which will automatically link all the sheets as hyperlink to the summary sheet. Any help would be appreciatd. thx! Boss |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hyperlink
Try the corrected version
Sub Macro1() Dim lngRow As Long, lngLastRow As Long Dim myRange As Range lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow Set myRange = Range("A" & lngRow) ActiveSheet.Hyperlinks.Add Anchor:=myRange, Address:="", SubAddress:= _ myRange.Text & "!A1", TextToDisplay:=myRange.Text Next End Sub If this post helps click Yes --------------- Jacob Skaria "Boss" wrote: This does not work... I need to click on A1 and it should open the corresponding worksheet in the same file. thx! Example: A1.value = "test" then it should open the worsheet test thx! "Jacob Skaria" wrote: Try the below macro in summary sheet.. Sub Macro() Dim lngRow As Long, lngLastRow As Long lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow ActiveSheet.Hyperlinks.Add Range("A" & lngRow), Range("A" & lngRow) Next End Sub If this post helps click Yes --------------- Jacob Skaria "Boss" wrote: I have ten sheets and a summary sheet. In the column A of summary sheet i have all the other sheets names. i need a code which will automatically link all the sheets as hyperlink to the summary sheet. Any help would be appreciatd. thx! Boss |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hyperlink
Thanks a lot... That worked perfectly.. How can i remove the TextToDisplay, if i wish to keep it as blank... thx! "Jacob Skaria" wrote: Try the corrected version Sub Macro1() Dim lngRow As Long, lngLastRow As Long Dim myRange As Range lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow Set myRange = Range("A" & lngRow) ActiveSheet.Hyperlinks.Add Anchor:=myRange, Address:="", SubAddress:= _ myRange.Text & "!A1", TextToDisplay:=myRange.Text Next End Sub If this post helps click Yes --------------- Jacob Skaria "Boss" wrote: This does not work... I need to click on A1 and it should open the corresponding worksheet in the same file. thx! Example: A1.value = "test" then it should open the worsheet test thx! "Jacob Skaria" wrote: Try the below macro in summary sheet.. Sub Macro() Dim lngRow As Long, lngLastRow As Long lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow ActiveSheet.Hyperlinks.Add Range("A" & lngRow), Range("A" & lngRow) Next End Sub If this post helps click Yes --------------- Jacob Skaria "Boss" wrote: I have ten sheets and a summary sheet. In the column A of summary sheet i have all the other sheets names. i need a code which will automatically link all the sheets as hyperlink to the summary sheet. Any help would be appreciatd. thx! Boss |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hyperlink
If text to display is set as blank as below; the full reference will be visible
TextToDisplay:="" So if we keep a blank space ( as below) a underscore will be visible. TextToDisplay:=" " If this post helps click Yes --------------- Jacob Skaria "Boss" wrote: Thanks a lot... That worked perfectly.. How can i remove the TextToDisplay, if i wish to keep it as blank... thx! "Jacob Skaria" wrote: Try the corrected version Sub Macro1() Dim lngRow As Long, lngLastRow As Long Dim myRange As Range lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow Set myRange = Range("A" & lngRow) ActiveSheet.Hyperlinks.Add Anchor:=myRange, Address:="", SubAddress:= _ myRange.Text & "!A1", TextToDisplay:=myRange.Text Next End Sub If this post helps click Yes --------------- Jacob Skaria "Boss" wrote: This does not work... I need to click on A1 and it should open the corresponding worksheet in the same file. thx! Example: A1.value = "test" then it should open the worsheet test thx! "Jacob Skaria" wrote: Try the below macro in summary sheet.. Sub Macro() Dim lngRow As Long, lngLastRow As Long lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow ActiveSheet.Hyperlinks.Add Range("A" & lngRow), Range("A" & lngRow) Next End Sub If this post helps click Yes --------------- Jacob Skaria "Boss" wrote: I have ten sheets and a summary sheet. In the column A of summary sheet i have all the other sheets names. i need a code which will automatically link all the sheets as hyperlink to the summary sheet. Any help would be appreciatd. thx! Boss |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hyperlink
You helped me a lot... thx! "Jacob Skaria" wrote: If text to display is set as blank as below; the full reference will be visible TextToDisplay:="" So if we keep a blank space ( as below) a underscore will be visible. TextToDisplay:=" " If this post helps click Yes --------------- Jacob Skaria "Boss" wrote: Thanks a lot... That worked perfectly.. How can i remove the TextToDisplay, if i wish to keep it as blank... thx! "Jacob Skaria" wrote: Try the corrected version Sub Macro1() Dim lngRow As Long, lngLastRow As Long Dim myRange As Range lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow Set myRange = Range("A" & lngRow) ActiveSheet.Hyperlinks.Add Anchor:=myRange, Address:="", SubAddress:= _ myRange.Text & "!A1", TextToDisplay:=myRange.Text Next End Sub If this post helps click Yes --------------- Jacob Skaria "Boss" wrote: This does not work... I need to click on A1 and it should open the corresponding worksheet in the same file. thx! Example: A1.value = "test" then it should open the worsheet test thx! "Jacob Skaria" wrote: Try the below macro in summary sheet.. Sub Macro() Dim lngRow As Long, lngLastRow As Long lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow ActiveSheet.Hyperlinks.Add Range("A" & lngRow), Range("A" & lngRow) Next End Sub If this post helps click Yes --------------- Jacob Skaria "Boss" wrote: I have ten sheets and a summary sheet. In the column A of summary sheet i have all the other sheets names. i need a code which will automatically link all the sheets as hyperlink to the summary sheet. Any help would be appreciatd. thx! Boss |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hyperlink
Cheers! If this post helps click Yes --------------- Jacob Skaria "Boss" wrote: You helped me a lot... thx! "Jacob Skaria" wrote: If text to display is set as blank as below; the full reference will be visible TextToDisplay:="" So if we keep a blank space ( as below) a underscore will be visible. TextToDisplay:=" " If this post helps click Yes --------------- Jacob Skaria "Boss" wrote: Thanks a lot... That worked perfectly.. How can i remove the TextToDisplay, if i wish to keep it as blank... thx! "Jacob Skaria" wrote: Try the corrected version Sub Macro1() Dim lngRow As Long, lngLastRow As Long Dim myRange As Range lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow Set myRange = Range("A" & lngRow) ActiveSheet.Hyperlinks.Add Anchor:=myRange, Address:="", SubAddress:= _ myRange.Text & "!A1", TextToDisplay:=myRange.Text Next End Sub If this post helps click Yes --------------- Jacob Skaria "Boss" wrote: This does not work... I need to click on A1 and it should open the corresponding worksheet in the same file. thx! Example: A1.value = "test" then it should open the worsheet test thx! "Jacob Skaria" wrote: Try the below macro in summary sheet.. Sub Macro() Dim lngRow As Long, lngLastRow As Long lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow ActiveSheet.Hyperlinks.Add Range("A" & lngRow), Range("A" & lngRow) Next End Sub If this post helps click Yes --------------- Jacob Skaria "Boss" wrote: I have ten sheets and a summary sheet. In the column A of summary sheet i have all the other sheets names. i need a code which will automatically link all the sheets as hyperlink to the summary sheet. Any help would be appreciatd. thx! Boss |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can't make hyperlink function work for hyperlink to website | Excel Worksheet Functions | |||
| Moving rows with Hyperlink doesn't move hyperlink address | Excel Discussion (Misc queries) | |||
| Intra-workbook hyperlink: macro/function to return to hyperlink ce | Excel Discussion (Misc queries) | |||
| Macro to Copy Hyperlink to another file as a HYPERLINK, not text... | Excel Programming | |||
| reading html when hyperlink address not hyperlink text diplayed | Excel Programming | |||
Linear Mode
