Can anyone VBA a formula for me please

[IanWells]

I have various folders that contain various workbooks with worksheets but are all designed the same.....

=SUM('Z:\Timesheets\Timesheets\Employees\Accounts\[Guilbert.xls]January'!$Z$5+'Z:\Timesheets\Timesheets\Employees\Accounts\[Shearman.xls]January'!$Z$5+'Z:\Timesheets\Timesheets\Employees\Accounts\[Royal.xls]January'!$Z$5)

This formula is fine when working with small amounts of workbooks but have serious time issues when trying to do the same when workbook numbers are great

I am thinking if i could get VBA code to run that through each folder and place totals in my specified cell in new workbook it would be better, also it would be a learning curve for me.

Please please help and guide me through this if its possible

Ian

 

[Andrew Poulsom]

What's makes you think that VBA will be quicker than formulas?

 

[D_Spark]

Hi if I have read this correct, I think what you are getting at is to create a summary sheet whereby cell Z5 values from each workbook can be loaded. You can then create a total from the data drawn forward.

If this is the case then you need to do the following:

Create a Unique folder where all the workbooks of the same structure are saved.
Create in this same area create a new Workbook called "Master Workbook"

for example
Create a workbook and name it
Master Analysis.xls
On this new workbook name one of the tabs
Auto_Update

Paste the following VB code into the VB editor (Worksheet Activate)

Private Sub Worksheet_Activate()
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Do not type or update anything on this sheet, clicking 'OK' will retrieve data from a central network area" ' Define message.
Style = vbOKOnly + vbCritical ' Define buttons.
Title = "WARNING" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)



Dim fso, fls, fl, vRng, i As Long, strPath As String
Dim strFile As String, j As Long
Range("A2:E" & Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
Set fso = CreateObject("scripting.filesystemobject")
Set fls = fso.GetFolder(ThisWorkbook.Path).Files
ReDim vRng(1 To fls.Count - 1, 1 To 5)
i = 0
For Each fl In fls
If (fl.Path <> ThisWorkbook.FullName And fl.Path <> "C:\Documents\Master Analysis.xls") Then
vRng(i + 1, 1) = fl.Path
i = i + 1
End If
Next fl
j = i
For i = 1 To j
strPath = Left$(vRng(i, 1), InStrRev(vRng(i, 1), "\"))
strFile = Replace(vRng(i, 1), strPath, vbNullString)
vRng(i, 2) = "='" & strPath & "[" & strFile & "]Comparison'!Z5"

Next
Range("A2").Resize(UBound(vRng, 1), 2) = vRng
End Sub

 

[IanWells]

Andrew

Some of the folders i want the totals from there could be 100+ workbooks contained....

Times that by 25 folders. then 12 sheets in each workbook ie jan/feb/march etc to dec..

Its basically monthly time sheets by department....

Need to add the total per person Z5 to Z10 in each worksheet(jan/feb etc) in each workbook(named) in each folder(department)

then master with total Z5 - jan/feb/mar
Z6 - jan/feb/mar
Z7 - "" ""

will try and explain it a little more when looked at the other reply mate

Ian

 

[IanWells]

D Spark

I think we may be very near mate, and i like the style......

am tryin to say it simple but think i may be gettin you confused or me, i dont really know lol......

folder with workbooks, the filenames called blue,red,green,brown etc

all contain sheets, jan/feb/mar up to december

cell z5 needs to total from blue,red,green,brown etc from worksheet JAN

i need to repeat this process from Z5 to Z10

once that is done i should be able to amend the code then for FEB and so on to DEC...

Does that make a little more sense mate....

Cheers Ian the Learner

 

[IanWells]

For those who are curious or interested i used the code below

#############################################

Option Explicit
Option Base 1 ' Set default array subscripts to 1.
Sub accounts()
Dim myDIR As String, fn As String
Dim wbk As Workbook
Dim MyRANGE As Range
Dim MyRANGE_Total As Range
Dim VarData(6)
Dim Range_Size As Integer
Dim I As Integer, J As Integer
Dim MONTH()
MONTH = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
Application.ScreenUpdating = False
myDIR = "Z:\Timesheets\Timesheets\Employees\Accounts\"
fn = Dir(myDIR & "*.xls")

For J = 1 To UBound(MONTH)
ThisWorkbook.Sheets(MONTH(J)).Range("C4:C9").ClearContents
Next J

Do While fn <> ""
If fn <> ThisWorkbook.Name Then
Set wbk = Workbooks.Open(myDIR & fn, UpdateLinks:=0)
For J = 1 To UBound(MONTH)
Set MyRANGE = wbk.Sheets(MONTH(J)).Range("Z5:Z10")
Set MyRANGE_Total = ThisWorkbook.Sheets(MONTH(J)).Range("C4:C9")
Range_Size = MyRANGE_Total.Rows.Count
For I = 1 To Range_Size
MyRANGE_Total.Cells(I, 1) = MyRANGE_Total.Cells(I, 1) + MyRANGE.Cells(I, 1)
Next I
Next J
wbk.Close False
End If
fn = Dir
Loop
Application.ScreenUpdating = True
End Sub

###########################################


Thanks to TCL