# Can anyone VBA a formula for me please

#### IanWells

##### Board Regular
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.

Ian

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Andrew Poulsom

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

#### D_Spark

##### Board Regular
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

##### Board Regular
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

##### Board Regular
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

##### Board Regular
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

Last edited:

Replies
1
Views
390
Replies
3
Views
231
Replies
3
Views
293
Replies
6
Views
316
Replies
0
Views
420

1,195,938
Messages
6,012,416
Members
441,698
Latest member
DaveTeo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back