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



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

Extracting Data - Open vs Closed
 

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" wrote in message
...

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

Extracting Data - Open vs Closed
 

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


--
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