Total or summing in a workbook within a folder
 

Hello,

I have a folder that contains about 25 workbooks, currently each workbook I
do the sum function and put the work "Total" in column A. Each of the
workbooks may have 1 or 50 rows. I still have open each workbook and put
totals (only two columns for totaling) and the word "Total" in column A. Is
it possible to have a global vba coding to put totals in all the workbook
and have the word "Total"? Any tips or visit a web site will be appreciate.
Thank you in advance. I don't think this is easy.

Total or summing in a workbook within a folder
 

Hi Cam

So which column are you putting the Sum function in, Column B? Is
the word 'total' going at the very bottom of a supposed list of
descriptive names in Column A?

Take care

Marcus

Total or summing in a workbook within a folder
 

HI Marcus,

Thanks for asking. Actually column D and column E will summed and on the
same line of the summed column A will have the word "Total" that column is
called "Name". Thanks

Cheers

"marcus" wrote in message
...
Hi Cam

So which column are you putting the Sum function in, Column B? Is
the word 'total' going at the very bottom of a supposed list of
descriptive names in Column A?

Take care

Marcus

Total or summing in a workbook within a folder
 

Hi Cam

OK so here we go. This opens all the files in a specific directory
and places “Total” at the bottom of column A, sums the numbers in
Columns D and E placing the formula in the same row as column A. It
assumes the Numbers start in Row 2 so you may have to change this to
suit if necessary.

You will need to change the file path “.Lookin = “C:\Yourpathhere
No need to prefix your path with .xls as this is catered for.

It saves the changes at present but you might like to run a trial with
a sample of files which open make the changes and do not save.

wkbk.Close SaveChanges:=False

Good luck with it.

Take care

Marcus


Sub OpenFiles()

Dim wkbk As Workbook
Dim Lw As Integer, Sr As Integer

With Application.FileSearch
.NewSearch
.LookIn = "c:\users\smallman\excel"
.SearchSubFolders = False
.Filename = ".xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Set wkbk = Workbooks.Open(.FoundFiles(i))
Lw = Range("A" & Rows.Count).End(xlUp).Row + 1
Sr = Lw - 1 'for the Sum row
Range("A" & Lw).Value = "Total"
Range("D" & Lw).Value = "=SUM(D2:D" & Sr & ")"
Range("E" & Lw).Value = "=SUM(E2:E" & Sr & ")"

wkbk.Close SaveChanges:=True
Next i

End If
End With
End Sub

Total or summing in a workbook within a folder
 

I think this will do what you want:
http://www.rondebruin.nl/copy4.htm


Regards,
Ryan---

--
RyGuy


"marcus" wrote:

Hi Cam

OK so here we go. This opens all the files in a specific directory
and places €œTotal€� at the bottom of column A, sums the numbers in
Columns D and E placing the formula in the same row as column A. It
assumes the Numbers start in Row 2 so you may have to change this to
suit if necessary.

You will need to change the file path €œ.Lookin = €œC:\Yourpathhere
No need to prefix your path with .xls as this is catered for.

It saves the changes at present but you might like to run a trial with
a sample of files which open make the changes and do not save.

wkbk.Close SaveChanges:=False

Good luck with it.

Take care

Marcus


Sub OpenFiles()

Dim wkbk As Workbook
Dim Lw As Integer, Sr As Integer

With Application.FileSearch
.NewSearch
.LookIn = "c:\users\smallman\excel"
.SearchSubFolders = False
.Filename = ".xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Set wkbk = Workbooks.Open(.FoundFiles(i))
Lw = Range("A" & Rows.Count).End(xlUp).Row + 1
Sr = Lw - 1 'for the Sum row
Range("A" & Lw).Value = "Total"
Range("D" & Lw).Value = "=SUM(D2:D" & Sr & ")"
Range("E" & Lw).Value = "=SUM(E2:E" & Sr & ")"

wkbk.Close SaveChanges:=True
Next i

End If
End With
End Sub