VBA excel worksheet2 out of range even though the cell is within the range and additional value created

[Vvy]

Hi VBA expert, I'm testing to combine different cells value from multiple workbooks into one summary sheet vertically. Below modified code based on the sharing from other thread. However, I'm getting out of range error for worksheet2 and also additional value appear in cell D8. Can you please advise what's the reason and how to fix?

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 = xxxx

'Define destination sheet

Set destSheet = ActiveWorkbook.Worksheets("MasterData")

r = 0

Application.ScreenUpdating = False

folderPath = Left(matchWorkbooks, InStrRev(matchWorkbooks, "\"))
wbFileName = Dir(matchWorkbooks)
While wbFileName <> vbNullString
Set fromWorkbook = Workbooks.Open(folderPath & wbFileName)

With fromWorkbook
    destSheet.Range("D4").Offset(r).Value = Worksheets(1).Range("B7").Value
    destSheet.Range("D5").Offset(r).Value = Worksheets(1).Range("C7").Value
    destSheet.Range("E4").Offset(r).Value = Worksheets(1).Range("B8").Value
    destSheet.Range("E5").Offset(r).Value = Worksheets(1).Range("C8").Value
    destSheet.Range("B4").Offset(r).Value = Worksheets(2).Range("C3").Value
    destSheet.Range("B5").Offset(r).Value = Worksheets(2).Range("C3").Value
    r = r + 2

End With


fromWorkbook.Close savechanges:=False
DoEvents
wbFileName = Dir
Wend

Application.ScreenUpdating = True

MsgBox "Finished"

End Sub

 

[myall_blues]

Without seeing your input data I can't explain where the B comes from, but the error is most likely because one of your input files (fromWorkbook) does not have two worksheets i.e. the reference to Worksheets(2) causes a subscript error.

 

[MARK858]

If Worksheets(2) is on fromWorkbook you need a period in front of it.
The same with Worksheets(1)

 

[Vvy]

Thank you for your response , the workbook has sheet2.

May I know what do you mean period in front like (1). there's period behind like (1).

 

[MARK858]

A full stop in front of Worksheets(1) and Worksheets(2) I e .Worksheets(2).

You'd probably be better off using the sheets name rather than it's Index though.