Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through all workbooks
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping | Excel Discussion (Misc queries) | |||
Looping through 2 workbooks | Excel Discussion (Misc queries) | |||
Looping | Excel Discussion (Misc queries) | |||
Looping through all workbooks | Excel Programming | |||
VBA code for looping through open workbooks and worksheets | Excel Programming |