Hello,

The following code is from Ron De Bruin's website, http://www.rondebruin.nl/summary2.htm . I am using it to generate a summary from different workbooks and at the same time generate/create links in the summary workbook.

My question is, how do I select the first sheet of every workbook, irrespective of it's name? I tried changing ShName = "Sheet1" but to no avail.

Sub Summary_cells_from_Different_Workbooks_1()
    Dim FileNameXls As Variant
    Dim SummWks As Worksheet
    Dim ColNum As Integer
    Dim myCell As Range, Rng As Range
    Dim RwNum As Long, FNum As Long, FinalSlash As Long
    Dim ShName As String, PathStr As String
    Dim SheetCheck As String, JustFileName As String
    Dim JustFolder As String

    ShName = "Sheet1"  '<---- Change
    Set Rng = Range("A1,D5:E5,Z10")    '<---- Change

    'Select the files with GetOpenFilename
    FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", _
                                              MultiSelect:=True)

    If IsArray(FileNameXls) = False Then
        'do nothing
    Else
        With Application
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
        End With

        'Add a new workbook with one sheet for the Summary
        Set SummWks = Workbooks.Add(1).Worksheets(1)

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

        For FNum = LBound(FileNameXls) To UBound(FileNameXls)
            ColNum = 1
            RwNum = RwNum + 1
            FinalSlash = InStrRev(FileNameXls(FNum), "\")
            JustFileName = Mid(FileNameXls(FNum), FinalSlash + 1)
            JustFolder = Left(FileNameXls(FNum), FinalSlash - 1)

            'copy the workbook name in column A
            SummWks.Cells(RwNum, 1).Value = JustFileName

            'build the formula string
            JustFileName = WorksheetFunction.Substitute(JustFileName, "'", "''")
            PathStr = "'" & JustFolder & "\[" & JustFileName & "]" & ShName & "'!"

            On Error Resume Next
            SheetCheck = ExecuteExcel4Macro(PathStr & Range("A1").Address(, , xlR1C1))
            If Err.Number <> 0 Then
                'If the sheet not exist in the workbook the row color will be Yellow.
                SummWks.Cells(RwNum, 1).Resize(1, Rng.Cells.Count + 1) _
                        .Interior.Color = vbYellow
            Else
                For Each myCell In Rng.Cells
                    ColNum = ColNum + 1
                    SummWks.Cells(RwNum, ColNum).Formula = _
                    "=" & PathStr & myCell.Address
                Next myCell
            End If
            On Error GoTo 0
        Next FNum

        ' Use AutoFit to set the column width in the new workbook
        SummWks.UsedRange.Columns.AutoFit

        MsgBox "The Summary is ready, save the file if you want to keep it"

        With Application
            .Calculation = xlCalculationAutomatic
            .ScreenUpdating = True
        End With
    End If
End Sub
Thank you