total value of a column in all the sheets to be consolidated in another worksheet

Hi,

can some one assist with my requirement.

i have an workbook with 6 worksheets. I need to find the sum of value in column no 10 after the last used row.

with the below code i can get the total only for the active sheet. Am not sure how to loop through all the sheets to have the total value inall the sheets.
[VBA]

Sub sum()

Dim LastRow As Long
Dim iRow As Long
Dim iCol As Integer
LastRow = 0
Dim ws As Worksheet

For Each ws In Worksheets

'Find last row
For iCol = 1 To 76
iRow = Cells(65536, iCol).End(xlUp).Row
If iRow > LastRow Then LastRow = iRow
Next iCol

With Application.WorksheetFunction
For iCol = 30 To 30
Cells(LastRow + 1, iCol) = .sum(Range(Cells(1, iCol), Cells(LastRow, iCol)))
Next iCol
End With

Next ws

End Sub
[/VBA]

Once we found the total of values in column 30, i need to append only the total amount in the excel file which is in different folder. Total from all the sheets to a single sheet.

-Sindhuja

This should do the addition in all the Sheets for Column J.

[vba]
Sub AddAllTogether()
Dim i As Long
Dim lrow As Long
Application.ScreenUpdating = False
For i = 1 To Worksheets.Count
With Worksheets(i)
lrow = .Range("J" & Rows.Count).End(xlUp).Row
.Range("J" & lrow).Offset(1, 0).Formula = "=SUM(J1:J" & lrow & ")"
End With
Next i
Application.ScreenUpdating = True
End Sub
[/vba]
I am confused (which does not take much) about Columns 10 and/or 30.

Thank you so much jolivanes.

It was column 30 for which I need the sum. Then all these sum need to entered in the master sheet which is in the different folder.

The user need to select the file to which all these sum needs to be added.

Kindly assist with this also pls...

-Sinduja

Hopefully someone will come by and sanitize this.

Code:

---

Sub TotalsIntoNewBook()
    Dim i As Long
    Dim j As Long
    Dim z As Integer
    Dim fn As String
    Dim MyArray()
    z = Worksheets.Count
    ReDim MyArray(1 To z)
    For j = 1 To z
        For i = 1 To z
            MyArray(i) = Worksheets(j).Range("AD" & Rows.Count).End(xlUp).Value  '<--- Column AD = Column 30
            j = j + 1
        Next i
    Next j
    fn = Application.GetOpenFilename("C:\,*.xls", 1, "Select One File To Open", , False)
    If TypeName(fn) = "Boolean" Then Exit Sub
    Workbooks.Open fn
    Range("E2").Resize(UBound(MyArray)) = Application.Transpose(MyArray)  '<--- Change cell address to suit
End Sub

---

You can try this also. You will get the totals in the 2nd Workbook only.
You don't have to total before you copy to the 2nd Workbook.

Code:

---

Sub SumAndArrayIntoNewBook()
    Dim i As Long
    Dim j As Long
    Dim z As Integer
    Dim fn As String
    Dim MyArray()
    z = Worksheets.Count
    ReDim MyArray(1 To z)
    For j = 1 To z
        For i = 1 To z
            MyArray(i) = WorksheetFunction.Sum(Worksheets(j).Range("AD1:AD" & Worksheets(j).Cells(Rows.Count, "AD").End(xlUp).Row))  '<--- Column AD = Column 30
            j = j + 1
        Next i
    Next j
    fn = Application.GetOpenFilename("C:\,*.xls", 1, "Select One File To Open", , False)
    If TypeName(fn) = "Boolean" Then Exit Sub
    Workbooks.Open fn
    'Range("E2").Resize(UBound(MyArray)) = Application.Transpose(MyArray)  '<--- Change cell address to suit. Hardcoded
 Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Resize(UBound(MyArray)) = Application.Transpose(MyArray)  '<--- Change cell address to suit. Pastes In First Empty Cell
End Sub

---

Thanks for the coding :)

I think i have not made my requirement clear.

I need to place the subtotals in the respective sheet of the newly opened workbook after the last used column in row no 5.

for eg: i have 3 sheets named sheetA, sheetB and sheetC in which i have found the subtotal using the coding you have provided. Now i have opened the new file in which i have to search for the sheet name "sheetA" and place the subtotal there. Need to do the same for all the sheets.

Can you pls assist me with this. I have tried modified the coding but it didnt worked forme.

-Sindhuja

The amended code is below and the results are not as expected. Can you please assist

[VBA]

Sub SumAndArrayIntoNewBook()
Dim fn As String
Dim I As Long
Dim j As Long
Dim z As Integer

Dim MyArray()
Dim wb As Workbook

z = Worksheets.Count
ReDim MyArray(1 To z)
For j = 1 To z
For I = 1 To z
MyArray(I) = WorksheetFunction.Sum(Worksheets(j).Range("AD1:AD" & Worksheets(j).Cells(Rows.Count, "AD").End(xlUp).Row))
MsgBox MyArray(I)
j = j + 1
Next I
Next j
fn = Application.GetOpenFilename("C:\,*.xls", 1, "Select One File To Open", , False)
If TypeName(fn) = "Boolean" Then Exit Sub
Workbooks.Open fn
MsgBox fn


Dim k As Long
Dim lcol As Integer
Application.ScreenUpdating = False
For k = 2 To Worksheets.Count
MsgBox Worksheets.Count
MsgBox Worksheets(k).Name

With Worksheets(k).UsedRange
lcol = .Range("A5").End(xlToRight).Offset(0, 1).Column
MsgBox lcol
Range(lcol).Resize(UBound(MyArray)) = MyArray(I)

End With

Next k

Application.ScreenUpdating = True

End Sub
[/VBA]

Quote:
Now i have opened the new file in which i have to search for the sheet name "sheetA" and place the subtotal there. Need to do the same for all the sheets.
Unquote.

and

Code:

---

For k = 2 To Worksheets.Count

---

It's probably me but I dont understand the relation here.

Could you explain in which sheets you would like the pasting done.
i.e. All sheets except the first sheet or in SheetA and all sheets after that or whatever.

Hi,

What i need is i should search for the sheet name (eg sheetA) in the newly opened file and place the subtotal value in all that sheet after the loast used column. this has to be done for all sheets

i need to exclude the first sheet in the nely opened file as it was the instruction sheet just has the information abt the file..

Thanks
sindhuja

Is this what you have in mind?

Code:

---

Sub SumAndArrayIntoNewBook()
    Dim i As Long
    Dim j As Long
    Dim z As Integer
    Dim fn As String
    Dim MyArray()
    Dim LastColumn As Range
    Dim k As Long
   
    z = Worksheets.Count
    ReDim MyArray(1 To z)
    For j = 1 To z
        For i = 1 To z
            MyArray(i) = WorksheetFunction.Sum(Worksheets(j).Range("C1:C" & Worksheets(j).Cells(Rows.Count, "C").End(xlUp).Row))
            j = j + 1
        Next i
    Next j
    fn = Application.GetOpenFilename("C:\,*.xls", 1, "Select One File To Open", , False)
    If TypeName(fn) = "Boolean" Then Exit Sub
    Workbooks.Open fn
   
For k = 2 To Worksheets.Count
With Sheets(k)
    Set LastColumn = Sheets(k).Cells(5, Sheets(k).Columns.Count).End(xlToLeft).Columns.Offset(, 1)
        LastColumn.Cells.Resize(UBound(MyArray)) = Application.Transpose(MyArray)
End With
Next k
   
End Sub

---

I have 2 files

- Source with sheets sheetA,sheetB,sheetC,sheetD
- Master with sheets instructions,sheetA,sheetB,sheetC,sheetD

Step1 : Find the subtotal of column AD in the source file in all the sheets
Step2 : insert the subtotal value to the respective sheet in the master file. Eg. The value of column AD in sheetA is 520. this need to entered in the sheetA of master file as 520 after the last used column.

The same to be done for all the sheets in source file and the values to be in master file.

Hope i made my requirement clear this time. Kinldy assist.

The coding provide displays the results as 0 that too all the subtotal values in the singly sheet.

Kindly assist me pls..

-Sindhuja

Did you change the cell references?

Code:

---

    For j = 1 To z
        For i = 1 To z
            MyArray(i) = WorksheetFunction.Sum(Worksheets(j).Range("C1:C" & Worksheets(j).Cells(Rows.Count, "C").End(xlUp).Row))
            j = j + 1
        Next i
    Next j

---

In your case, I think this should be

Code:

---

    For j = 1 To z
        For i = 1 To z
            MyArray(i) = WorksheetFunction.Sum(Worksheets(j).Range("AD1:AD" & Worksheets(j).Cells(Rows.Count, "AD").End(xlUp).Row))
            j = j + 1
        Next i
    Next j

---

And if you say now that it needs to be entered in all sheets, change

Code:

---

For k = 2 To Worksheets.Count    '<---- Change the 2 to a 1 for all the sheets
With Sheets(k)
    'In the following line, it looks at the fifth cell down to determine the last Column. Change the 5 to _
    a 1 if you want to determine the last Column from the top cell.
    Set LastColumn = Sheets(k).Cells(5, Sheets(k).Columns.Count).End(xlToLeft).Columns.Offset(, 1)
        'If you want to paste the subtotals starting at the fifth cell down, change the next line to _
        LastColumn.Cells(5).Resize(UBound(MyArray)) = Application.Transpose(MyArray)
        'LastColumn.Cells.Resize(UBound(MyArray)) = Application.Transpose(MyArray)
End With
Next k

---

Clear as mud?

Hi,

I have attached the sample source sheet and the master sheet with the expected results.

I tried with the provided coding but the results are not as expected.

please assist. The expected results are highlighted in yellow.

only sheet1 total in the sheet1 of master sheet
only sheet2 total in the sheet2 of master sheet
and so on..

Sorry to bother you again and again on my requirement :(

-Sindhuja

sindhuja
Unfortunately I am strapped for time. I will not have internet access for the next +/- 5 weeks. (Yes, this is 2012 after all)
I am sure someone will come by and supply you with the code needed.
If not, it will be a while for me before I can finish this.

To sum it all up what you need:
1) You have two workbooks. Only one (Source) is open
2) You want to select a workbook (Master) to open with "Application.GetOpenFilename"
3) You want the total for Column AD (2 to last used cell) in Book "Source" pasted into Book "Master"
4) The pasting need to be in row 5 in the first empty cell (To the Right of the last used column in Row 5)
5) The Sheet Names are the same in both Workbooks. Book "Master" has one extra Sheet (First Sheet)
6) The totals from each Sheet in Workbook "Source" go in the same named Sheet in Workbook "Master"

Are the totals calculated already or do they need to be summed in the code?

Good luck

John

Yes John, Perfect !!


this is my requirement. It would be great if i can get the coding at the earliest. And the total calculations in the source file should be in the coding itself. .

Expecting at the earliest !!

-Sindhuja

See if this does it for now. A little over the top I guess but it might do until someone else comes around with a more appealing code.

Code:

---

Sub SumAndArrayIntoNewBook()
    Dim i As Variant
    Dim ii As Variant
    Dim j As Long
    Dim z As Integer
    Dim fn As String
    Dim MyArray()
    Dim LastColumn As Range
    Dim k As Long
 
    z = Worksheets.Count

    ReDim MyArray(1 To z)

    For j = 1 To z
        For i = 1 To z
            MyArray(i) = WorksheetFunction.Sum(Worksheets(j).Range("AD2:AD" & Worksheets(j).Cells(Rows.Count, "AD").End(xlUp).Row))
            j = j + 1
        Next i
    Next j

    fn = Application.GetOpenFilename("C:\,*.xls", 1, "Select One File To Open", , False)
    If TypeName(fn) = "Boolean" Then Exit Sub
    Workbooks.Open fn
 
ii = 1

For k = 2 To Worksheets.Count
With Sheets(k)
    Set LastColumn = Sheets(k).Cells(5, Sheets(k).Columns.Count).End(xlToLeft).Columns.Offset(, 1)
        LastColumn = MyArray(ii)
End With
ii = i + 1
Next k
End Sub

---

sindhuja.
Attached the workbooks you attached previously.
These seem to work.
You can add sheets to both workbooks if required.
Let me know, before I go, if this works for you.
Good Luck

John

Perfect John !! the results are as expected. One more query, does this takes the sheetname. if the sheet order differs in the master file than the source file will this display the correct result.

Thank you soooo much.. you always rock :)

sindhuja.
Good to hear that it works for you.
No, it does not use the Sheet names, strictly the order in which the Sheets are in.
If you want to, you could order the sheets before you use any code on them.
That should be a different topic (new thread) I think.

Good luck.
John