Looping through all workbooks
 

Patrick - your suggestion now works fine and I can do the processing I asked
about. There is one difficulty. While I am holding each of the files in the
loop I thought I ought to backup the file before processing it using
something like:

FileCopy SourceFile, DestinationFile

where SourceFile needs to be wbTemp and DestinationFile has the same name
but in a different directory. I cant figure out how to define the
sourcefile as wbtemp and to define the destinationfile either. I suspect its
because wbtemp is not a string but an actual workbook. Is that right? how
do I pass a workbook full name to a string?

"Patrick Molloy" wrote in message
...
Sub MultiCopy()
dim wbMaster as Workbook, wsMaster as worksheet
dim wbTemp as Workbook
dim wsTemp as worksheet
dim fn as string
dim rw as long

' set the initial file using
fn = DIR("C:\Root\Subfolders\*.xls")
' fn will hold the first file matching *.xls
if fn ="" then exit sub

set wbMaster = _
Workbooks.Open("C:\Pathtomaster\Master.xls")
set wsMaster = wbMaster.Activesheet

do until fn =""
set wbTemp = Workbooks.Open(fn)
set wsTemp = wbTempActivesheet
' copy data
' your code eg
' next free row in master
rw = wsMaster.Range("A1").End(xlDown).Row + 1
' say 2 x 10
with wsMaster
.cells(.cells(rw,1),.cells(rw+1,10)).Value = _
wsTemp.Range("A1:B10").Value
end with

' close temp book
wbTemp.Close false ' false means don't save
set wsTemp = nothing
set wbTemp = Nothing

' get the next file
fn = DIR
' fn will be empty if there are no more files
loop

msgbox "Done"

End Sub

HTH
Patrick Molloy
Microsoft Excel MVP



-----Original Message-----
Can someone give me a start to the code to open all the
workbooks in turn
that are in a specific directory and then to copy
specific cells to a
masterfile in a different directory? thanks in advance


.