Retrieving values for a range of cells in multiple workbooks

[niterider006]

Hi all, been trying to use a macro I've seen here to help me retrieve a range of values of all workbooks in the folder. However, I have 2 problems:
1. It only retrieves one cell and not a range of cells in multiple rows and columns.
2. It seems to hangup when the cell is located in a hidden column.

For example: My data has 4 product items located in row 37,43, 55 & 61. and contain a value for each month of the year including quarter totals in columns C through R. Here is the macro I've been using to retrieve just one cell:

Sub Projections()
' Adjust the path below as required
MyPath = "\\acctgfs-v01\share\Accounting\AD Revenue\Ad Projections\2017\"    ' Set the path.
myname = Dir(MyPath, vbNormal)    ' Retrieve the first entry.
Do While myname <> ""    ' Start the loop.
    ' Ignore the current directory and the encompassing directory.
    If myname <> "." And myname <> ".." Then
        If (GetAttr(MyPath & myname) And vbNormal) = vbNormal Then
           ActiveCell.FormulaR1C1 = "='" & MyPath & "[" & myname & "]Summary 2017 Gross'!R37C3" ' change the part after the ] to your sheets name
           ' also change the R1C1 on the end to pick up the cell you want ie R2C3 for cell C2
           ' do NOT change the 1st one (.FormulaR1C1) this is part of the command.
           ActiveCell.Offset(0, 1).Value = myname
           ActiveCell.Offset(1, 0).Select
        End If
    End If
    myname = Dir
Loop

 

[ravishankar]

try the macro from the following link with modifications to suit your needs
https://www.mrexcel.com/forum/excel-questions/437931-extract-data-closed-excel-files-vba.html
Ravishankar