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