Extracting Data - Open vs Closed

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

Please Login or Register  to view this content.

Thanks

VBA Noob

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
>

Thanks Ron.

Just what the doctor ordered.



PS I use your e-mail attachment code all the time. Works a treat too.

Keep up the good work

VBA Noob