Consulting

Results 1 to 19 of 19

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

  1. #1
    VBAX Mentor
    Joined
    Sep 2007
    Posts
    405
    Location

    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

  2. #2
    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.
    Last edited by Aussiebear; 04-09-2012 at 12:52 AM. Reason: Corrected the tags surrounding the submitted code

  3. #3
    VBAX Mentor
    Joined
    Sep 2007
    Posts
    405
    Location
    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

  4. #4
    Hopefully someone will come by and sanitize this.

    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

  5. #5
    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.

    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

  6. #6
    VBAX Mentor
    Joined
    Sep 2007
    Posts
    405
    Location
    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

  7. #7
    VBAX Mentor
    Joined
    Sep 2007
    Posts
    405
    Location
    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]

  8. #8
    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

    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.

  9. #9
    VBAX Mentor
    Joined
    Sep 2007
    Posts
    405
    Location
    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

  10. #10
    Is this what you have in mind?

    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

  11. #11
    VBAX Mentor
    Joined
    Sep 2007
    Posts
    405
    Location
    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
    Last edited by sindhuja; 04-13-2012 at 07:13 AM.

  12. #12
    Did you change the cell references?

        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

        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

    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?

  13. #13
    VBAX Mentor
    Joined
    Sep 2007
    Posts
    405
    Location
    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
    Attached Files Attached Files

  14. #14
    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

  15. #15
    VBAX Mentor
    Joined
    Sep 2007
    Posts
    405
    Location
    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

  16. #16
    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.

    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

  17. #17
    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
    Attached Files Attached Files

  18. #18
    VBAX Mentor
    Joined
    Sep 2007
    Posts
    405
    Location
    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

  19. #19
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •