Adding sheet name to this code

[mittmo]

I'm working with this code another user created a few years ago that extracts data from multiple sheets in a folder to another single workbook. Problem is that I'm not working off of the first sheet, but the 5th sheet. How do I do add that sheet name to this code?

Public Sub Copy_Values_From_Workbooks()

    Dim matchWorkbooks As String
    Dim destSheet As Worksheet, r As Long
    Dim folderPath As String
    Dim wbFileName As String
    Dim fromWorkbook As Workbook
  
    'Folder path and wildcard workbook files to import cells from
  
    matchWorkbooks = "C:\folder\path\*.xls"                                             'CHANGE THIS
  
    'Define destination sheet
  
    Set destSheet = ActiveWorkbook.Worksheets("Summary")                                'CHANGE THIS
  
    destSheet.Cells.Clear
    r = 0
  
    Application.ScreenUpdating = False
          
    folderPath = Left(matchWorkbooks, InStrRev(matchWorkbooks, "\"))
    wbFileName = Dir(matchWorkbooks)
    While wbFileName <> vbNullString
        Set fromWorkbook = Workbooks.Open(folderPath & wbFileName)
        With fromWorkbook.Worksheets(1)
            destSheet.Range("B8:G8").Offset(r).Value = .Range("Q10:V10").Value
            destSheet.Range("H8:M8").Offset(r).Value = .Range("Q13:V13").Value
            destSheet.Range("N8:S8").Offset(r).Value = .Range("Q16:V16").Value
            destSheet.Range("A8").Offset(r).Value = .Range("B14").Value
            r = r + 1
        End With
        fromWorkbook.Close savechanges:=False
        DoEvents
        wbFileName = Dir
    Wend
  
    Application.ScreenUpdating = True
  
    MsgBox "Finished"
  
End Sub

 

[Biz]

Sorry your question doesn't make sense.
The code below already selects the first worksheet in a workbook. If the worksheet you want to copy is in 5th worksheet then obviously it would extract data from wrong worksheet.
Please review your workbook structure.

With fromWorkbook.Worksheets(1)

Hope it makes sense.

Biz

 

[Alex Blakenburg]

You are currently accessing the sheet using the Index no in the line below.
(The index no is effectively the position in the workbook with the sheet furthest to the left being 1 )

        With fromWorkbook.Worksheets(1)

Simply substitute the index number for the name of the worksheet enclosing it in Quotation marks (")
eg if the sheet name is MySheet then it becomes

        With fromWorkbook.Worksheets("MySheet")