-
Mar 12th, 2021, 07:38 AM
#1
Thread Starter
New Member
-
Mar 12th, 2021, 03:39 PM
#2
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
-
Mar 17th, 2021, 10:24 AM
#3
Thread Starter
New Member
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
-
Mar 17th, 2021, 03:15 PM
#4
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
-
Mar 25th, 2021, 03:27 AM
#5
Thread Starter
New Member
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
-
Mar 25th, 2021, 03:50 AM
#6
Re: SUM data from multiple sheets VBA code
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|