Multiple Sheet Problem...help

[BrettOlbrys]

File #1 contains about 150 sheets. Each sheet sums the values in a column and puts the result in cell A20 (for all sheets the result is also in each sheets cell A20).

File #2 is a summary of the 150 sheets in file #.

I have two questions:

1) How do I have the names of each of the 150 sheets automatically appear in a column in file #2?

2) For each of the 150 sheets, how do I have each of the results in the respective cell A20 also appear in a column in file #2?

Thanks,

Brett

[Paul Sheppard]

File #1 contains about 150 sheets. Each sheet sums the values in a column and puts the result in cell A20 (for all sheets the result is also in each sheets cell A20).

File #2 is a summary of the 150 sheets in file #.

I have two questions:

1) How do I have the names of each of the 150 sheets automatically appear in a column in file #2?

2) For each of the 150 sheets, how do I have each of the results in the respective cell A20 also appear in a column in file #2?

Thanks,

Brett

Hi Brett

Try this Macro
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

Set Basebook = ActiveWorkbook
Set Newsh = Basebook.Worksheets.Add

On Error Resume Next
Newsh.Name = "Summary-Sheet"
If Err.Number > 0 Then
MsgBox "The Summary sheet already exist in this workbook."
With Application
.DisplayAlerts = False
Newsh.Delete
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
Exit Sub
End If

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

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

Newsh.Cells(RwNum, 1).Value = Sh.Name
'Copy the sheet name in the A column

For Each myCell In Sh.Range("A20") ' <----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