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?
VBA 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