On May 20, 7:52 pm, Madiya wrote:
On May 20, 3:58 am, James8309 wrote:





Hi everyone,

I was wondering if anyone can help with my problem.

I have a workbook containing three worksheets; 'sheet1', 'sheet2' and
'sheet3'. And;

1. On 'sheet1' I have name of the months from B5 to AO5
- i.e. Jan - 05 to Apr - 08 : I simply typed Jan 05 in B5 then
dragged to AO5.

2. 'sheet1' Cell A1 contains the name of the workbook "ABC123"

3. From 'sheet1" cell A6 till A25. I have codes in combination of
number and alphabet structure.
- i.e. OTO35, VOF05 etc ( 3 letters and 2 number at the end )

4. On my directory 'C:\Documents\Database' Under the Database folder
I have many different kinds of representative name folders such as
"ABC123", "DEF456", "ABB768" and so on.

5. Inside of this each name folder, It has 4 different year folders
called "2005", "2006", "2007" and "2008". Inside of these year
folders, I have excel files name in month as this "Jan 05", "Feb 05"
all the way to "Dec 05" and so on. It is same with other year folders
except the last two number digit from the name where in folder
"2007",
it will have excel file name "Jan 07" ~"Dec 07".

6. On 'sheet1' cell B6 which will be underneath cell B5(Jan-05), I
need to open 'C:\Documents\Database'\ABC123\2005\Jan 05.xls' file
then
do the sumif on cell A6 which contains the code "OTO35".

=== Here is the problem that I can't solve. What I can do with my
small brain in VBA is to actually tell it to open up the file under
directory 'C:\Documents\Database'\ABC123\2005\Jan 05.xls' then do
sumif of A6 on Jan 05.xls Range A:J and sum data column J, then put
it
on B6.

- Is there any possible way to program in VBA in such way that when
msgbox pops up at the very start and I just type in the file name
"ABC123" or refer to cell A1 or whatever, Open up the correct file on
correct month then do a sumif automatically?

i.e.

A. if file name is ABC123 for this report then go to path 'C:
\Documents
\Database'\ABC123\
if file name is DEF456 then go to path 'C:\Documents
\Database'\DEF456\

B. Then, if it is Jan-05 go to correct year folder under then open up
correct xls file. i.e. 'C:\Documents\Database'\ABC123\2005\Jan 05.xls

C. Do a sumif of A6 from Jan 05 file from Range A:J and sum data on
column J.

D. Repeat this step from Jan - 05 to Apr - 08 (B5 to AO5) then
getting
results cells underneath.

I am pretty sure there will be alot better and efficient way of doing
this.

If anyone can help or give me an advice how this can be done, that
would be wonderful!

Thanks guys!! :D

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("She et1").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!!:D )
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 !

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


"James8309" wrote:

On May 20, 7:52 pm, Madiya wrote:
On May 20, 3:58 am, James8309 wrote:





Hi everyone,

I was wondering if anyone can help with my problem.

I have a workbook containing three worksheets; 'sheet1', 'sheet2' and
'sheet3'. And;

1. On 'sheet1' I have name of the months from B5 to AO5
- i.e. Jan - 05 to Apr - 08 : I simply typed Jan 05 in B5 then
dragged to AO5.

2. 'sheet1' Cell A1 contains the name of the workbook "ABC123"

3. From 'sheet1" cell A6 till A25. I have codes in combination of
number and alphabet structure.
- i.e. OTO35, VOF05 etc ( 3 letters and 2 number at the end )

4. On my directory 'C:\Documents\Database' Under the Database folder
I have many different kinds of representative name folders such as
"ABC123", "DEF456", "ABB768" and so on.

5. Inside of this each name folder, It has 4 different year folders
called "2005", "2006", "2007" and "2008". Inside of these year
folders, I have excel files name in month as this "Jan 05", "Feb 05"
all the way to "Dec 05" and so on. It is same with other year folders
except the last two number digit from the name where in folder
"2007",
it will have excel file name "Jan 07" ~"Dec 07".

6. On 'sheet1' cell B6 which will be underneath cell B5(Jan-05), I
need to open 'C:\Documents\Database'\ABC123\2005\Jan 05.xls' file
then
do the sumif on cell A6 which contains the code "OTO35".

=== Here is the problem that I can't solve. What I can do with my
small brain in VBA is to actually tell it to open up the file under
directory 'C:\Documents\Database'\ABC123\2005\Jan 05.xls' then do
sumif of A6 on Jan 05.xls Range A:J and sum data column J, then put
it
on B6.

- Is there any possible way to program in VBA in such way that when
msgbox pops up at the very start and I just type in the file name
"ABC123" or refer to cell A1 or whatever, Open up the correct file on
correct month then do a sumif automatically?

i.e.

A. if file name is ABC123 for this report then go to path 'C:
\Documents
\Database'\ABC123\
if file name is DEF456 then go to path 'C:\Documents
\Database'\DEF456\

B. Then, if it is Jan-05 go to correct year folder under then open up
correct xls file. i.e. 'C:\Documents\Database'\ABC123\2005\Jan 05.xls

C. Do a sumif of A6 from Jan 05 file from Range A:J and sum data on
column J.

D. Repeat this step from Jan - 05 to Apr - 08 (B5 to AO5) then
getting
results cells underneath.

I am pretty sure there will be alot better and efficient way of doing
this.

If anyone can help or give me an advice how this can be done, that
would be wonderful!

Thanks guys!! :D

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("She et1").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!!:D )
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 !