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
47
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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Grant22

New Member
Joined
Dec 28, 2016
Messages
47
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.
 

Sektor

Well-known Member
Joined
May 6, 2011
Messages
2,834
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:

Grant22

New Member
Joined
Dec 28, 2016
Messages
47
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!
 

Sektor

Well-known Member
Joined
May 6, 2011
Messages
2,834
You're welcome!
hi.gif
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

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
Top