Need to Loop through sheets and add range for each sheet, then add it to master sheet

Grant22

New Member
Joined
Dec 28, 2016
Messages
48
Hello all, I need to loop through each sheet, copy the values in Range B4:M7 (they're constant, always numbers) and then add them all together in on the Home page. Basically it's been awhile since I've done loops so I'm forgetting how to add them up as you go then put them in the home sheet and getting an error on the code below. Any help is greatly appreciated. Thanks!

Code:
Dim Rng as Range
Dim RngTotal as Range
RngTotal = 0

Dim ws3 As Worksheet
    For Each ws3 In Sheets
        If ws3.Name <> "Home"
            Set Rng as ws3.Range("B5:M8")
        End If
    RngTotal = RngTotal + Rng
Next ws3

Sheets("Home").Range("B5:M8") = RngTotal
End Sub
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Also, just noticed in my code above I forgot to copy the sub name. It's in there, so that's not why the error keeps getting thrown. Just thought I'd clear that up.
 
Upvote 0
Code:
Sub K()
    
    Dim ws3 As Worksheet
    
    For Each ws3 In Sheets
        If ws3.Name <> "Home" Then
            ws3.Range("B5:M8").Copy
             Sheets("Home").Range("B5:M8").PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
        End If
    Next ws3
    
End Sub

There's a way to do it without macros - by using so called 3-D formula (for instance, for B5): =SUM(Sheet1:Sheet5!B5), where sheet Home must not be between Sheet1 and Sheet3.
 
Last edited:
Upvote 0
Thanks Sektor, worked like a charm. Yeah I thought about using that type of formula but it wouldn't work for this particular workbook because this workbook relies on the user creating the sheets before they're added to the home sheet. I was able to automate that process but since the workbook starts without any sheets to pull from, it would cause errors I think. Thanks again, this site is the best!
 
Upvote 0
You're welcome!
hi.gif
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top