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" <(E-Mail Removed)> wrote in message news:51B06CFA-1F8D-43B7-912E-(E-Mail Removed)...
> > 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
>
Similar Threads
