VBA : Sumif on different workbooks! *********

[James8309]

Hi,
Here is a code to get you started.
Pl change the variables as required to suit you.
I am no expert hance this may not be the best and most efficient code
but hope it will work for you.
======>>>>>> watch for line wraps <<<<<========
Sub TEST()
Dim CELL As Range
Dim LR As Integer
Dim MYPATH As String
Dim WB As Workbook
Dim MYREF As String
Dim SUMREF As String
MYPATH = "C:\DOCS\DATA\"
LR = Range("A65000").End(xlUp).Row
For Each CELL In Range("B6:H" & LR)
CELL.Select
SUMREF = Range("A" & CELL.Row).Value
CELL.Interior.ColorIndex = 33
MYPATH = MYPATH & Range("A1").Value & "\" & Year(Cells(5,
ActiveCell.Column).Value) & "\" & Format(Cells(5,
ActiveCell.Column).Value, "MMM YY")
Debug.Print MYPATH
MYREF = MYPATH & ".XLS"
Workbooks.Open Filename:=MYREF
Debug.Print MYREF
Set WB = ActiveWorkbook
CELL.Value =
Application.WorksheetFunction.SumIf(WB.Sheets("Sheet1").Range("A:J"),
SUMREF, WB.Sheets("Sheet1").Range("B:J"))
MYPATH = "C:\DOCS\DATA\"
WB.Close
Next
End Sub

Regards,
Madiya- Hide quoted text -

- Show quoted text -

Firstly, Thank you so much for your help!!

Your coding works beautifully.

Coding does all the sumif function, opening closing of workbooks from
correct directory s but if i have about 1000 rows, I have too many
cells to go one by one.

Is it possible to alter this code just a bit?

e.g.
- Dates in form of (Jan 05, Feb 05...) in the range ("B6:AO6")
- Sum reference value from A6 to A80

1. Opening up correct workbook from specific directory ( This works
wonderfully Thanks!! )
i.e. First one would be "Jan 05"

2. In Cell B6, Do Sumif with reference A6, on the workbook that's
opened (i.e. "Jan 05")

3. Autofill Column B6 to B80 ( This I believe will do all the sumif
for value A6:A80 on workbook that's just opened up),

4. Closing this "Jan 05" Workbook

5. Continuing the same process with C6, D6....AO6.


I was wondering if anyone can do msgbox input thing.

Thank you !

 

[Joel]

Try these changes

Sub TEST()

Dim CELL As Range
Dim LR As Integer
Dim MYPATH As String
Dim WB As Workbook
Dim FNAME As String
Dim SUMREF As String
Dim COLCOUNT As Long

MYPATH = "C:\DOCS\DATA\"
LR = Range("A" & Rows.Count).End(xlUp).Row

ThisSht = ThisWorkbook.ActiveSheet
COLCOUNT = 2 'column B
With ThisSht
FNAME = MYPATH & .Range("A1").Value & "\" & _
Year(.Cells(5, COLCOUNT).Value) & "\" & _
Format(.Cells(5, COLCOUNT).Value, "MMM YY")
Debug.Print FNAME
FNAME = FNAME & ".XLS"
Set WB = Workbooks.Open(Filename:=FNAME)
Debug.Print FNAME

For Each CELL In .Range(.Cells(6, COLCOUNT), .Cells(LR, COLCOUNT))
SUMREF = .Range("A" & CELL.Row).Value
CELL.Interior.ColorIndex = 33
CELL.Value = Application.WorksheetFunction. _
SumIf(WB.Sheets("Sheet1").Range("A:J"), _
SUMREF, WB.Sheets("Sheet1").Range("B:J"))
Next CELL
WB.Close
COLCOUNT = COLCOUNT + 1
End With
End Sub