Read through workbooks in a folder

Hello

I'm using a slightly modified code that I found on this forum, to look through all the .xlsm files in a folder, but I need help to access specific cell in two different sheets.

'Written: June 27, 2011
'Author:  Leith Ross
'Thread:  http://www.excelforum.com/excel-programming/781876-vba-read-all-excel-files-in-a-directory-then-parse-through-each.html
'Poster:  kpierce

Sub CopyFromWorkbooks()

  Dim DstRng As Range
  Dim DstWks As Worksheet
  Dim R As Long
  Dim SrcRng As Range
  Dim SrcWks As Worksheet
  Dim SrcWkb As Workbook
  Dim FileName As String
  Dim FilePath As String
  Dim Folder As Variant
  Dim Title As String
  
    Set DstWks = ActiveSheet
    Set DstRng = DstWks.Range("A2")
    
    Set RngEnd = DstWks.Cells(Rows.Count, DstRng.Column).End(xlUp)
    Set DstRng = IIf(RngEnd = "", DstRng, RngEnd.Offset(1, 0))
  
    
      FilePath = "path"
      FileName = Dir("path" & "\*.xlsm")
      
      If FileName = "" Then
         MsgBox "No Excel workbooks were found in this directory.", vbCritical
         Exit Sub
      End If
      
      Application.ScreenUpdating = False
      
        Do While FileName <> ""
          On Error GoTo ErrorExit
          Set SrcWkb = Workbooks.Open(FileName:=FilePath & "\" & FileName, ReadOnly:=True)
            For Each SrcWks In SrcWkb.Worksheets
              DstRng.Offset(R, 0) = SrcWks.Range("D4")
              DstRng.Offset(R, 1) = SrcWks.Range("F4")
              DstRng.Offset(R, 2) = SrcWks.Range("B26")
              DstRng.Offset(R, 3) = SrcWks.Range("G6")
              DstRng.Offset(R, 4) = SrcWks.Range("A6")
              DstRng.Offset(R, 5) = SrcWks.Range("G4")
              DstRng.Offset(R, 6) = SrcWks.Range("A1")
              R = R + 1
            Next SrcWks
          SrcWkb.Close False
          FileName = Dir()
        Loop
        
ErrorExit:
      Application.ScreenUpdating = True
      If Err <> 0 Then
         MsgBox "Run-time error '" & Err.Number & "':" & vbCrLf & Err.Description
      End If
  
End Sub

This bit of code works wonders as long as there is only one sheet I have to pull data from.
The problem arises when I need to access data from sheet2. As the loop is now, it does all the calculations on each sheet and I don't really know how to specify what sheet to draw information from.


I've edited the code below to reflect what cell that should be accessed on each sheet.

Do While FileName <> ""
          On Error GoTo ErrorExit
          Set SrcWkb = Workbooks.Open(FileName:=FilePath & "\" & FileName, ReadOnly:=True)
            For Each SrcWks In SrcWkb.Worksheets
              DstRng.Offset(R, 0) = SrcWks.Range("D4") 'This should be read from sheet1
              DstRng.Offset(R, 1) = SrcWks.Range("F4") 'This should be read from sheet1
              DstRng.Offset(R, 2) = SrcWks.Range("B26") 'This should be read from sheet1
              DstRng.Offset(R, 3) = SrcWks.Range("G6") 'This should be read from sheet1
              DstRng.Offset(R, 4) = SrcWks.Range("A6") 'This should be read from sheet1
              DstRng.Offset(R, 5) = SrcWks.Range("G4") 'This should be read from sheet1
              DstRng.Offset(R, 6) = SrcWks.Range("A1") 'This should be read from sheet2
              R = R + 1
            Next SrcWks
          SrcWkb.Close False
          FileName = Dir()
        Loop

Any help would be greatly appreciated.