Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Adding hyper links from summary page to tab?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 69
Default Adding hyper links from summary page to tab?
Good day, I am using this VB script to create my summary page

http://www.rondebruin.nl/summary.

Column A has the customer numbers that are the sheet tab names. what I need
to do now is have each customer number in the summary worksheet hyperlink to
the corresponding worksheet (worksheet tab) everytime the worksheet is rebuild

Any suggestions?

Thanks in advance


--
Nelson
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,123
Default Adding hyper links from summary page to tab?
Try this one

Sub Summary_All_Worksheets_With_Formulas()
Dim Sh As Worksheet
Dim Newsh As Worksheet
Dim myCell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim Basebook As Workbook

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Delete the sheet "Summary-Sheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Summary-Sheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "Summary-Sheet"
Set Basebook = ThisWorkbook
Set Newsh = Basebook.Worksheets.Add
Newsh.Name = "Summary-Sheet"

'The links to the first sheet will start in row 2
RwNum = 1

For Each Sh In Basebook.Worksheets
If Sh.Name < Newsh.Name And Sh.Visible Then
ColNum = 1
RwNum = RwNum + 1
'Copy the sheet name in the A column
ActiveSheet.Hyperlinks.Add Anchor:=Newsh.Cells(RwNum, 1), Address:="", SubAddress:= _
Sh.Name & "!A1", TextToDisplay:=Sh.Name & "!A1"

For Each myCell In Sh.Range("A1,D5:E5,Z10") '<--Change the range
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Next myCell

End If
Next Sh

Newsh.UsedRange.Columns.AutoFit

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Nelson" wrote in message ...
Good day, I am using this VB script to create my summary page

http://www.rondebruin.nl/summary.

Column A has the customer numbers that are the sheet tab names. what I need
to do now is have each customer number in the summary worksheet hyperlink to
the corresponding worksheet (worksheet tab) everytime the worksheet is rebuild

Any suggestions?

Thanks in advance


--
Nelson
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,123
Default Adding hyper links from summary page to tab?
After a private mail with a verry smart Excel Guru I decide
to add code this week to the webpage that use the Hyperlink worksheet function.

Check it out this week if you want

Good night

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Ron de Bruin" wrote in message ...
Try this one

Sub Summary_All_Worksheets_With_Formulas()
Dim Sh As Worksheet
Dim Newsh As Worksheet
Dim myCell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim Basebook As Workbook

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Delete the sheet "Summary-Sheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Summary-Sheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "Summary-Sheet"
Set Basebook = ThisWorkbook
Set Newsh = Basebook.Worksheets.Add
Newsh.Name = "Summary-Sheet"

'The links to the first sheet will start in row 2
RwNum = 1

For Each Sh In Basebook.Worksheets
If Sh.Name < Newsh.Name And Sh.Visible Then
ColNum = 1
RwNum = RwNum + 1
'Copy the sheet name in the A column
ActiveSheet.Hyperlinks.Add Anchor:=Newsh.Cells(RwNum, 1), Address:="", SubAddress:= _
Sh.Name & "!A1", TextToDisplay:=Sh.Name & "!A1"

For Each myCell In Sh.Range("A1,D5:E5,Z10") '<--Change the range
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Next myCell

End If
Next Sh

Newsh.UsedRange.Columns.AutoFit

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Nelson" wrote in message ...
Good day, I am using this VB script to create my summary page

http://www.rondebruin.nl/summary.

Column A has the customer numbers that are the sheet tab names. what I need
to do now is have each customer number in the summary worksheet hyperlink to
the corresponding worksheet (worksheet tab) everytime the worksheet is rebuild

Any suggestions?

Thanks in advance


--
Nelson
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Adding hyper links from summary page to tab?
Maybe with a credit to David McRitchie, too???

He was the first person I saw that used the =hyperlink() worksheet function with
that kind of subaddress.



Ron de Bruin wrote:

After a private mail with a verry smart Excel Guru I decide
to add code this week to the webpage that use the Hyperlink worksheet function.

Check it out this week if you want

Good night

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"Ron de Bruin" wrote in message ...
Try this one

Sub Summary_All_Worksheets_With_Formulas()
Dim Sh As Worksheet
Dim Newsh As Worksheet
Dim myCell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim Basebook As Workbook

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Delete the sheet "Summary-Sheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Summary-Sheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "Summary-Sheet"
Set Basebook = ThisWorkbook
Set Newsh = Basebook.Worksheets.Add
Newsh.Name = "Summary-Sheet"

'The links to the first sheet will start in row 2
RwNum = 1

For Each Sh In Basebook.Worksheets
If Sh.Name < Newsh.Name And Sh.Visible Then
ColNum = 1
RwNum = RwNum + 1
'Copy the sheet name in the A column
ActiveSheet.Hyperlinks.Add Anchor:=Newsh.Cells(RwNum, 1), Address:="", SubAddress:= _
Sh.Name & "!A1", TextToDisplay:=Sh.Name & "!A1"

For Each myCell In Sh.Range("A1,D5:E5,Z10") '<--Change the range
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Next myCell

End If
Next Sh

Newsh.UsedRange.Columns.AutoFit

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Nelson" wrote in message ...
Good day, I am using this VB script to create my summary page

http://www.rondebruin.nl/summary.

Column A has the customer numbers that are the sheet tab names. what I need
to do now is have each customer number in the summary worksheet hyperlink to
the corresponding worksheet (worksheet tab) everytime the worksheet is rebuild

Any suggestions?

Thanks in advance


--
Nelson

--

Dave Peterson
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 69
Default Adding hyper links from summary page to tab?
Thanks, I am missing something here and not seeing it, any suggestions

Dim Newsh As Worksheet
Dim myCell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim Basebook As Workbook

With Application
..Calculation = xlCalculationManual
..ScreenUpdating = False
End With

'Delete the sheet "Summary-Sheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Summary-Sheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "Summary-Sheet"
Set Basebook = ThisWorkbook
Set Newsh = Basebook.Worksheets.Add
Newsh.Name = "Summary-Sheet"

'The links to the first sheet will start in row 2
RwNum = 1

With Newsh.Cells
..HorizontalAlignment = xlCenter
..VerticalAlignment = xlCenter
End With
With Newsh.Range("A1:K1")
..Value = Array("Customer Number", "Renewal Quarter", _
"Customer Name", "ABC 2007", "ABC 2007", "ABC 2008", "ABC 2008", "ABC 2009",
"ABC 2009", "ABC 2010", "ABC 2010")
..Interior.ColorIndex = 15
..Font.Bold = True
..AutoFilter
End With

'Add headers

Newsh.Range("A1:K1").Value = Array("Customer Number", "Renewal Quarter",
"Customer Name", "PIM 2007", "CDI 2007", "PIM 2008", "CDI 2008", "PIM 2009",
"CDI 2009", "PIM 2010", "CDI 2010")

For Each Sh In Basebook.Worksheets
If Sh.Name < Newsh.Name And Sh.Visible And Sh.Index 3 Then
ColNum = 1
RwNum = RwNum + 1

'Copy the sheet name in the A column
ActiveSheet.Hyperlinks.Add Anchor:=Newsh.Cells(RwNum, 1), Address:="",
SubAddress:= _
Sh.Name & "!A1", TextToDisplay:=Sh.Name & "!A1"

For Each myCell In Sh.Range("A1,D5:E5,Z10") '<--Change the range
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Next myCell

End If
Next Sh

Newsh.UsedRange.Columns.AutoFit

With Application
..Calculation = xlCalculationAutomatic
..ScreenUpdating = True
End With
End Sub




--
Nelson


"Ron de Bruin" wrote:

Try this one

Sub Summary_All_Worksheets_With_Formulas()
Dim Sh As Worksheet
Dim Newsh As Worksheet
Dim myCell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim Basebook As Workbook

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Delete the sheet "Summary-Sheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Summary-Sheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "Summary-Sheet"
Set Basebook = ThisWorkbook
Set Newsh = Basebook.Worksheets.Add
Newsh.Name = "Summary-Sheet"

'The links to the first sheet will start in row 2
RwNum = 1

For Each Sh In Basebook.Worksheets
If Sh.Name < Newsh.Name And Sh.Visible Then
ColNum = 1
RwNum = RwNum + 1
'Copy the sheet name in the A column
ActiveSheet.Hyperlinks.Add Anchor:=Newsh.Cells(RwNum, 1), Address:="", SubAddress:= _
Sh.Name & "!A1", TextToDisplay:=Sh.Name & "!A1"

For Each myCell In Sh.Range("A1,D5:E5,Z10") '<--Change the range
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Next myCell

End If
Next Sh

Newsh.UsedRange.Columns.AutoFit

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Nelson" wrote in message ...
Good day, I am using this VB script to create my summary page

http://www.rondebruin.nl/summary.

Column A has the customer numbers that are the sheet tab names. what I need
to do now is have each customer number in the summary worksheet hyperlink to
the corresponding worksheet (worksheet tab) everytime the worksheet is rebuild

Any suggestions?

Thanks in advance


--
Nelson

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 69
Default Adding hyper links from summary page to tab?
EXCELLENT THANKS! Got it working...
--
Nelson


"Nelson" wrote:

Thanks, I am missing something here and not seeing it, any suggestions

Dim Newsh As Worksheet
Dim myCell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim Basebook As Workbook

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Delete the sheet "Summary-Sheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Summary-Sheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "Summary-Sheet"
Set Basebook = ThisWorkbook
Set Newsh = Basebook.Worksheets.Add
Newsh.Name = "Summary-Sheet"

'The links to the first sheet will start in row 2
RwNum = 1

With Newsh.Cells
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
With Newsh.Range("A1:K1")
.Value = Array("Customer Number", "Renewal Quarter", _
"Customer Name", "ABC 2007", "ABC 2007", "ABC 2008", "ABC 2008", "ABC 2009",
"ABC 2009", "ABC 2010", "ABC 2010")
.Interior.ColorIndex = 15
.Font.Bold = True
.AutoFilter
End With

'Add headers

Newsh.Range("A1:K1").Value = Array("Customer Number", "Renewal Quarter",
"Customer Name", "PIM 2007", "CDI 2007", "PIM 2008", "CDI 2008", "PIM 2009",
"CDI 2009", "PIM 2010", "CDI 2010")

For Each Sh In Basebook.Worksheets
If Sh.Name < Newsh.Name And Sh.Visible And Sh.Index 3 Then
ColNum = 1
RwNum = RwNum + 1

'Copy the sheet name in the A column
ActiveSheet.Hyperlinks.Add Anchor:=Newsh.Cells(RwNum, 1), Address:="",
SubAddress:= _
Sh.Name & "!A1", TextToDisplay:=Sh.Name & "!A1"

For Each myCell In Sh.Range("A1,D5:E5,Z10") '<--Change the range
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Next myCell

End If
Next Sh

Newsh.UsedRange.Columns.AutoFit

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub




--
Nelson


"Ron de Bruin" wrote:

Try this one

Sub Summary_All_Worksheets_With_Formulas()
Dim Sh As Worksheet
Dim Newsh As Worksheet
Dim myCell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim Basebook As Workbook

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Delete the sheet "Summary-Sheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Summary-Sheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "Summary-Sheet"
Set Basebook = ThisWorkbook
Set Newsh = Basebook.Worksheets.Add
Newsh.Name = "Summary-Sheet"

'The links to the first sheet will start in row 2
RwNum = 1

For Each Sh In Basebook.Worksheets
If Sh.Name < Newsh.Name And Sh.Visible Then
ColNum = 1
RwNum = RwNum + 1
'Copy the sheet name in the A column
ActiveSheet.Hyperlinks.Add Anchor:=Newsh.Cells(RwNum, 1), Address:="", SubAddress:= _
Sh.Name & "!A1", TextToDisplay:=Sh.Name & "!A1"

For Each myCell In Sh.Range("A1,D5:E5,Z10") '<--Change the range
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Next myCell

End If
Next Sh

Newsh.UsedRange.Columns.AutoFit

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Nelson" wrote in message ...
Good day, I am using this VB script to create my summary page

http://www.rondebruin.nl/summary.

Column A has the customer numbers that are the sheet tab names. what I need
to do now is have each customer number in the summary worksheet hyperlink to
the corresponding worksheet (worksheet tab) everytime the worksheet is rebuild

Any suggestions?

Thanks in advance


--
Nelson

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,123
Default Adding hyper links from summary page to tab?
I do that Dave


"Dave Peterson" schreef in bericht
...
Maybe with a credit to David McRitchie, too???

He was the first person I saw that used the =hyperlink() worksheet
function with
that kind of subaddress.



Ron de Bruin wrote:

After a private mail with a verry smart Excel Guru I decide
to add code this week to the webpage that use the Hyperlink worksheet
function.

Check it out this week if you want

Good night

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"Ron de Bruin" wrote in message
...
Try this one

Sub Summary_All_Worksheets_With_Formulas()
Dim Sh As Worksheet
Dim Newsh As Worksheet
Dim myCell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim Basebook As Workbook

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Delete the sheet "Summary-Sheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Summary-Sheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "Summary-Sheet"
Set Basebook = ThisWorkbook
Set Newsh = Basebook.Worksheets.Add
Newsh.Name = "Summary-Sheet"

'The links to the first sheet will start in row 2
RwNum = 1

For Each Sh In Basebook.Worksheets
If Sh.Name < Newsh.Name And Sh.Visible Then
ColNum = 1
RwNum = RwNum + 1
'Copy the sheet name in the A column
ActiveSheet.Hyperlinks.Add Anchor:=Newsh.Cells(RwNum, 1),
Address:="", SubAddress:= _
Sh.Name & "!A1",
TextToDisplay:=Sh.Name & "!A1"

For Each myCell In Sh.Range("A1,D5:E5,Z10") '<--Change
the range
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Next myCell

End If
Next Sh

Newsh.UsedRange.Columns.AutoFit

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Nelson" wrote in message
...
Good day, I am using this VB script to create my summary page

http://www.rondebruin.nl/summary.

Column A has the customer numbers that are the sheet tab names. what I
need
to do now is have each customer number in the summary worksheet
hyperlink to
the corresponding worksheet (worksheet tab) everytime the worksheet is
rebuild

Any suggestions?

Thanks in advance


--
Nelson

--

Dave Peterson

__________ Information from ESET Smart Security, version of virus
signature database 4148 (20090611) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus signature database 4148 (20090611) __________

The message was checked by ESET Smart Security.

http://www.eset.com



Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,123
Default Adding hyper links from summary page to tab?
FYI

I update the page today with two ways to add a hyperlink
http://www.rondebruin.nl/summary.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Nelson" wrote in message ...
Good day, I am using this VB script to create my summary page

http://www.rondebruin.nl/summary.

Column A has the customer numbers that are the sheet tab names. what I need
to do now is have each customer number in the summary worksheet hyperlink to
the corresponding worksheet (worksheet tab) everytime the worksheet is rebuild

Any suggestions?

Thanks in advance


--
Nelson
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using buttons to instead of hyper links Melisa Hutchins Links and Linking in Excel 1 January 24th 11 10:39 PM
Excel Hyper Links Cblasingame Excel Worksheet Functions 1 March 12th 08 09:32 PM
Converting test to numbers and adding into a summary page Laura Excel Worksheet Functions 1 September 7th 06 04:24 PM
Hyper Links sweetcircuit Excel Worksheet Functions 2 June 13th 05 03:35 PM
Hyper Links Tim[_17_] Excel Programming 0 July 25th 03 05:57 PM


All times are GMT +1. The time now is 12:06 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement