Hi

Start here
http://www.rondebruin.nl/copy3.htm

Post back if you have problems



--
Regards Ron De Bruin
http://www.rondebruin.nl



"VBA Noob" <VBA.Noob.29dw0z_1150274703.2982@excelforum-nospam.com> wrote in message
news:VBA.Noob.29dw0z_1150274703.2982@excelforum-nospam.com...
>
> Hi,
>
> I've got 20 spreadsheets called Week 1 to Week 20 each has two sheets
> called Wk 42 T and Week 42 R. They are stored in a shared area on the K
> Drive. I want to copy all the data from Column M in both sheets (around
> 180 to 200 lines) and then transfer the data to a workbook called
> "summary" with two sheets called "Totals T" & "Total R".
>
> I've was thinking of using the below code to open the workbooks but is
> this the best way??.
>
> If it is the best way I need help with selecting the data from the
> Workbooks called Week 1 to Week 20 in the K drive and then looping
> through each workbook and the two worksheets and pasting that data in
> Column A in "Totals T" (all info from Wk 42 T) & "Total R" (all info
> from Wk 42 R) in the workbook called "Summary"
>
> Any help greatly appreciated
>
>
>
> Code:
> --------------------
>
> Sub RunCodeOnAllXLSFiles()
> Dim lCount As Long
> Dim wbResults As Workbook
> Dim wbCodeBook As Workbook
>
> Application.ScreenUpdating = False
> Application.DisplayAlerts = False
> Application.EnableEvents = False
>
> On Error Resume Next
>
> Set wbCodeBook = ThisWorkbook
>
> With Application.FileSearch
> .NewSearch
> 'Change path to suit
> .LookIn = "C:\MyDocuments\TestResults"
> .FileType = msoFileTypeExcelWorkbooks
> '.Filename = "Book*.xls"
>
> If .Execute > 0 Then 'Workbooks in folder
> For lCount = 1 To .FoundFiles.Count 'Loop through all.
> 'Open Workbook x and Set a Workbook variable to it
> Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
>
> 'CODE HERE
>
> wbResults.Close SaveChanges:=True
>
> Next lCount
> End If
> End With
>
> On Error Goto 0
> Application.ScreenUpdating = True
> Application.DisplayAlerts = True
> Application.EnableEvents = True
> End Sub
>
> --------------------
>
>
>
> Thanks
>
> VBA Noob
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=551766
>