Sub Test3()

Dim CELL As Range
Dim LR As Integer
Dim MYPATH As String
Dim WB As Workbook
Dim MYREF As String
Dim SUMREF As String
Dim Lastrow As Long
Dim WS As Worksheet
Folder = "C:\Mydocuments\ABC\"
Lastrow = Range("A" & Rows.Count).End(xlUp).Row

For RowCount = 6 To Lastrow
For Each CELL In Range("B" & RowCount & ":AO" & RowCount)
SUMREF = CELL.Offset(0, -1)
CELL.Interior.ColorIndex = 25
MYPATH = Folder & Range("A1").Value & "\" & _
Year(Cells(5, CELL.Column).Value) & "\" _
& Format(Cells(5, CELL.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("H:U"), _
SUMREF, _
WB.Sheets("Sheet1").Range("U:U"))
WB.Close
Next CELL
Next RowCount

End Sub


"James8309" wrote:

Hi, everyone.

I am having difficulties trying to fit in Autofill codes in between my
sumif macro.

My sumif macro works fine and it does;

1. Open up correct data workbook from specific directory then.

2. Do a sumif in each cell Range from B7:AO7 to the Last row that
contains data with criteria on column A

3. Close data workbook.

Basically it opens up a correct file, perform a sumif then close
workbook. I am just trying to fit 'Autofill' in. so instead of closing
the data workbook straight away after performing sumif in each cell,
it does autofill for that column then closes the workbook.

Where do I squeeze the autofill in? I was thinking after the
"CELL.Value =
Application.WorksheetFunction.SumIf(WB.Sheets("She et1").Range("H:U"),
SUMREF, WB.Sheets("Sheet1").Range("U:U"))" line.


Sub Test3()

Dim CELL As Range
Dim LR As Integer
Dim MYPATH As String
Dim WB As Workbook
Dim MYREF As String
Dim SUMREF As String
Dim Lastrow As Long
Dim WS As Worksheet
MYPATH = "C:\Mydocuments\ABC\"
LR = Range("A65000").End(xlUp).Row
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row

For Each CELL In Range("B6:AO6")
CELL.Select
SUMREF = Range("A" & CELL.Row).Value
CELL.Interior.ColorIndex = 25
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("She et1").Range("H:U"),
SUMREF, WB.Sheets("Sheet1").Range("U:U"))

MYPATH = "C:\Mydocuments\ABC\"
WB.Close
Next

End Sub