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

 
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

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