Total or summing in a workbook within a folder

[CAM]

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.

 

[marcus]

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

 

[CAM]

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]

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" & Sr & ")"
Range("E" & Lw).Value = "=SUM(E2:E" & Sr & ")"

wkbk.Close SaveChanges:=True
Next i

End If
End With
End Sub

 

[ryguy7272]

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


Regards,
Ryan---