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

#### Grant22

##### New Member
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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### Grant22

##### New Member
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
Code:
``````Sub K()

Dim ws3 As Worksheet

For Each ws3 In Sheets
If ws3.Name <> "Home" Then
ws3.Range("B5:M8").Copy
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
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
You're welcome!

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,426
Messages
5,837,162
Members
430,480
Latest member
iangessey

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

### Which adblocker are you using?

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

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