Something like this should work:
Option Explicit
Sub ImportData()
'This macro is installed into the master workbook
Dim fPATH As String, fNAME As String, NR As Long
Dim wbDATA As Workbook, wsMASTER As Worksheet
Set wsMASTER = ThisWorkbook.Sheets("Sheet1") 'edit this to the name of the master sheet
NR = wsMASTER.Range("A" & Rows.Count).End(xlUp).Row + 1 'next empty row on the master sheet
fPATH = "\\c\folder\" 'path to all files
fNAME = Dir(fPATH & "*.xls*") 'get the first filename from the folder
Do While Len(fNAME) > 0 'process one file at a time til no more filenames
Set wbDATA = Workbooks.Open(fPATH * fNAME) 'open the found file
With Range("13:15,30:32")
.EntireRow.Hidden = False 'unhide the rows
.Copy wsMASTER.Range("A" & NR) 'copy into master sheet to empty rows
End With
wsMASTER.Range("A" & NR).Resize(6).Value = Range("B4").Value 'add key value in column A
wbDATA.Close False 'close the found file
NR = wsMASTER.Range("A" & Rows.Count).End(xlUp).Row + 1 'next empty row on the master sheet
fNAME = Dir 'get the next filename
Loop
End Sub
Bookmarks