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