Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
summing total sales formula | Excel Discussion (Misc queries) | |||
summing 3 worksheets on a total sheet | Excel Discussion (Misc queries) | |||
summing a row, remove lowest values from the total | Excel Discussion (Misc queries) | |||
Problem with summing formula updating Total... | Excel Discussion (Misc queries) | |||
Need help summing the total of two columns with their products | Excel Discussion (Misc queries) |