V
VBA Noob
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
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