Thanks Tom. So I can amend it for my particular needs, can you please tell
me what the different parts of this line do?

Set rng = ThisWorkbook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp)(2)

I presume I can change the first (1) to ("Sheet Name") to go to the sheet I
want, but what do the next 2 expressions do exactly? I want them to go to
the next empty cell in column A after cell A14 for the pasting action. Not
familiar with the End(xlUp)(2) expression at all.

Thanks in advance,

Nick


"Tom Ogilvy" wrote:

assume they are all stored in a single folder and you want to process all
files in that folder. the master workbook is not in that folder. Master
workbook contains the code and data is copied to the first sheet in the tab
order, starting in the next available cell in column A. Data to be copied
from each workbook is in the first sheet in the tab order in cells A1:F20

Sub GetData()
Dim sPath as String, sName as String
Dim rng as Range, bk as Workbook
sPath = "C:\Myfolder\"
sname = dir(sPath & "*.xls")
do while sName < ""
set rng = thisworkbook.worksheets(1).Cells(rows.count,1).End (xlup)(2)
set bk = workbooks.Open(sPath & sname)
bk.worksheets(1).Range("A1:F20").copy rng
bk.close Savechanges:=False
sName = dir()
Loop
End Sub


Adjust to suit your actual situation.
--
Regards,
Tom Ogilvy


"Nick Smith" wrote:

What I mean is that I have several excel files (sub-workbooks) with different
file names saved on a network drive. It is the cell data contained in one
sheet of each of these files that I need to import to a master file.

"Ardus Petus" wrote:

I presume what you call subworkbooks are worksheets

Regards,
--
AP

"Nick Smith" a écrit dans le message
de ...
Hi,

Is it possible to have a macro for this:

1. Master workbook contains a sheet listing a set of sub-workbooks as
http
links
2. Open linked sub-workbooks (either all at once or individually)
3. Copy defined range of cells from defined sheet name in opened
sub-workbook to the master workbook (data only, not formatting). (Range
and
sheet name is same for all sub-workbooks). Do this for all the linked
workbooks without overwriting any of the previous copied cells.
4. Close sub-workbook(s)

Alternatively, can this be done with the workbooks all stored as files ina
named network directory?

Thanks,

Nick


that will open all workbooks listed on a sheet as http links, copy a
defined set of cell data from one of the sheets into the master workbook,
and
then close the workbook.