Results 1 to 6 of 6

Thread: SUM data from multiple sheets VBA code

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2021
    Posts
    12

    SUM data from multiple sheets VBA code

    Hi Guys,

    I really hope someone can help me out on this relative simple VBA code (simple for more experienced users, which I am not).

    I want to consoildate the data from specific cells (always same location) coming from multiple sheets (WK2106 / WK2107 / etc.) into 1 mastersheet. The sheets are reflecting the working weeks and they hold the spend amount of hours of that week per project. The goal of the mastersheet (in this case called 'Overzicht') will show the sum of all these hours. I have attached the below pictures for clarification purpose.

    I would really appreciate when somebody can provide the VBA code (and perhaps even provides an explaination).

    Name:  Sheets.jpg
Views: 206
Size:  35.0 KB
    Name:  Overzicht.jpg
Views: 174
Size:  52.7 KB

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: SUM data from multiple sheets VBA code

    as you already know the ranges to add, have you tried the worksheetfunction.sum(rng1, rng2, rng3)
    where the ranges are fully qualified
    Code:
    set rng1 = sheets("wk2106").range("F5")
    i am probably not using any correct range and you can put the range directly into the sum function if you don't need to use object variables
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Feb 2021
    Posts
    12

    Re: SUM data from multiple sheets VBA code

    Hi,

    I found the solution and felt like sharing this with you. Probably not the most 'sexy and refined' code, but it does the job. Maybe someone has a tip on how to shorten this code:




    Sub MasterSheet()

    'Declare variables
    Dim ws As Worksheet
    ActiveWindow.DisplayZeros = False

    'Set variables/ values
    Worksheets("Overzicht").Range("F5") = 0
    Worksheets("Overzicht").Range("F6") = 0
    Worksheets("Overzicht").Range("F7") = 0
    Worksheets("Overzicht").Range("F8") = 0
    Worksheets("Overzicht").Range("F9") = 0
    Worksheets("Overzicht").Range("F10") = 0
    Worksheets("Overzicht").Range("F11") = 0
    Worksheets("Overzicht").Range("F12") = 0
    Worksheets("Overzicht").Range("F13") = 0
    Worksheets("Overzicht").Range("F14") = 0
    Worksheets("Overzicht").Range("F15") = 0
    Worksheets("Overzicht").Range("F16") = 0
    Worksheets("Overzicht").Range("F17") = 0
    Worksheets("Overzicht").Range("F18") = 0
    Worksheets("Overzicht").Range("F19") = 0
    Worksheets("Overzicht").Range("F20") = 0
    Worksheets("Overzicht").Range("F21") = 0
    Worksheets("Overzicht").Range("F22") = 0
    Worksheets("Overzicht").Range("F23") = 0

    For Each ws In ActiveWorkbook.Worksheets

    If ws.Name Like "WK*" Then

    Worksheets("Overzicht").Range("F5") = Worksheets("Overzicht").Range("F5") + ws.Range("N7")
    Worksheets("Overzicht").Range("F6") = Worksheets("Overzicht").Range("F6") + ws.Range("N8")
    Worksheets("Overzicht").Range("F7") = Worksheets("Overzicht").Range("F7") + ws.Range("N9")
    Worksheets("Overzicht").Range("F8") = Worksheets("Overzicht").Range("F8") + ws.Range("N10")
    Worksheets("Overzicht").Range("F9") = Worksheets("Overzicht").Range("F9") + ws.Range("N11")
    Worksheets("Overzicht").Range("F10") = Worksheets("Overzicht").Range("F10") + ws.Range("N12")
    Worksheets("Overzicht").Range("F11") = Worksheets("Overzicht").Range("F11") + ws.Range("N13")
    Worksheets("Overzicht").Range("F12") = Worksheets("Overzicht").Range("F12") + ws.Range("N14")
    Worksheets("Overzicht").Range("F13") = Worksheets("Overzicht").Range("F13") + ws.Range("N15")
    Worksheets("Overzicht").Range("F14") = Worksheets("Overzicht").Range("F14") + ws.Range("N16")
    Worksheets("Overzicht").Range("F15") = Worksheets("Overzicht").Range("F15") + ws.Range("N17")
    Worksheets("Overzicht").Range("F16") = Worksheets("Overzicht").Range("F16") + ws.Range("N18")
    Worksheets("Overzicht").Range("F17") = Worksheets("Overzicht").Range("F17") + ws.Range("N19")
    Worksheets("Overzicht").Range("F18") = Worksheets("Overzicht").Range("F18") + ws.Range("N20")
    Worksheets("Overzicht").Range("F19") = Worksheets("Overzicht").Range("F19") + ws.Range("N21")
    Worksheets("Overzicht").Range("F20") = Worksheets("Overzicht").Range("F20") + ws.Range("N22")
    Worksheets("Overzicht").Range("F21") = Worksheets("Overzicht").Range("F21") + ws.Range("N23")
    Worksheets("Overzicht").Range("F22") = Worksheets("Overzicht").Range("F22") + ws.Range("N24")
    Worksheets("Overzicht").Range("F23") = Worksheets("Overzicht").Range("F23") + ws.Range("N25")

    End If


    Next

    End Sub

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: SUM data from multiple sheets VBA code

    you could try like
    Code:
    For rw = 5 To 23
        Worksheets("Overzicht").Range("F" & rw) = 0
    Next
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name Like "WK*" Then
            For rw = 5 To 23
                Worksheets("Overzicht").Range("F5") = Worksheets("Overzicht").Range("F" & rw) + ws.Range("N" & rw + 2)
            Next
        End If
    Next
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    New Member
    Join Date
    Feb 2021
    Posts
    12

    Re: SUM data from multiple sheets VBA code

    Hi Westconn1,

    Thanks so much. I found 1 small error, but now it is a elegant code.

    code:
    For rw = 5 To 23
    Worksheets("Overzicht").Range("F" & rw) = 0
    Next
    For Each ws In ActiveWorkbook.Worksheets
    If ws.Name Like "WK*" Then
    For rw = 5 To 23
    Worksheets("Overzicht").Range("F" & rw) = Worksheets("Overzicht").Range("F" & rw) + ws.Range("N" & rw + 2)
    Next
    End If
    Next

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: SUM data from multiple sheets VBA code

    well spotted and fixed
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Click Here to Expand Forum to Full Width