The following code uses ExecuteExcel4Macro to retrieve the values from a closed workbook. With more than 30 sheets, though, you may find it somewhat slow. In any case, try adopting the following code and see how well it works for you. Note that it is assumed that the "current workbook" is the active workbook. Also, you should turn off screen updating, and include the appropriate error handling.
Option Explicit
Sub test()
Dim sFullName As String
Dim sPath As String
Dim sFile As String
Dim vRanges As Variant
Dim rRange As Range
Dim ShtIndx As Integer
Dim RngIndx As Integer
Dim r As Long
Dim c As Long
sFullName = "c:\users\domenic\desktop\sample.xlsm"
sPath = Left(sFullName, InStrRev(sFullName, "\"))
sFile = Mid(sFullName, InStrRev(sFullName, "\") + 1)
vRanges = Array("C8:AG16", "C19:AG21", "C29:AG26", "C29:AG32")
For ShtIndx = 5 To 34
With Worksheets(ShtIndx)
For RngIndx = 0 To UBound(vRanges)
Set rRange = .Range(vRanges(RngIndx))
For r = 1 To rRange.Rows.Count
For c = 1 To rRange.Columns.Count
rRange(r, c).Value = GetValue(sPath, sFile, .Name, rRange(r, c).Address(, , xlR1C1))
.Range("O2").Value = GetValue(sPath, sFile, .Name, .Range("Q2").Address(, , xlR1C1))
Next c
Next r
Next RngIndx
End With
Next ShtIndx
End Sub
Private Function GetValue(sPath, sFile, sSheet, sRef)
Dim sArg As String
If Right(sPath, 1) <> "\" Then
sPath = sPath & "\"
End If
sArg = "'" & sPath & "[" & sFile & "]" & sSheet & "'!" & sRef
GetValue = ExecuteExcel4Macro(sArg)
End Function
Hope this helps!