About Us

Mervyn Thomas[_3_]

Many thanks - about to test

Mervyn

"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

.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Looping	Maggie[_6_]	Excel Discussion (Misc queries)	6	October 2nd 08 09:14 PM
Looping through 2 workbooks	Pierre	Excel Discussion (Misc queries)	3	March 7th 08 04:00 PM
Looping	David T	Excel Discussion (Misc queries)	2	August 30th 06 10:51 PM
Looping through all workbooks	Abdul Salam[_3_]	Excel Programming	1	July 29th 03 02:59 PM
VBA code for looping through open workbooks and worksheets	Jamie Martin[_2_]	Excel Programming	1	July 24th 03 06:44 PM

Menu

About Us