Grand Total from Different Worksbooks

[Plastik808]

Hello,

I have a folder, containing 120 workbooks(which only contain 1 sheet each). Each workbook represents a traders PnL for the year so there are 10 traders.

I need to find a way of writing some code that will look into each workbook and grab the total at the bottom (which has traders name attached) and placing this in a seperate workbook. The code i have below works if all the traders PnL are contained with the same workbook, so im asking how can i modify this to look at all workbooks contained in a folder?

Dim employee As String, total As Integer, sheet As Worksheet, i As Integer
total = 0
employee = InputBox("Enter the employee name (case sensitive)")
For Each sheet In Worksheets
For i = 2 To 13
If sheet.Cells(i, 2).Value = employee Then
total = total + sheet.Cells(i, 3).Value
End If
Next i
Next sheet
MsgBox "Total sales of " & employee & " is " & total

End Sub




many thanks in advance!

 

[Silvrhelm]

http://www.ozgrid.com/VBA/loop-through.htm

Looks like that link has the info you need

 

[Plastik808]

As im new to VBA i usually place a button on the sheet and code from there...for some reason it doesnt like the 1st line??

Private Sub CommandButton1_Click()
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:\Risk Projects\VBA\Scanning Workbooks"
.FileType = msoFileTypeExcelWorkbooks
'Optional filter with wildcard
'.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)
Dim employee As String, total As Integer, i As Integer
total = 0
employee = InputBox("Enter the employee name")
For Each Sheet In Worksheets
For i = 1 To 13
If Sheet.Cells(i, 1).Value = employee Then
total = total + Sheet.Cells(i, 2).Value

Next i

Next Sheet


wbResults.Close SaveChanges:=False
Next lCount
End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
MsgBox "Total sales of " & employee & " is " & total

End Sub