Hi, I would like to sum every nth cell in each individual sheet and paste in the master sheet.

Sheet 1 - Sum(I26 + I32 + I38 + etc..)

Sheet 2 - Sum(I26 + I32 + I38 + etc..)

Sheet 3 - Sum(I26 + I32 + I38 + etc..)

Master Sheet - Cell (A1) - Sheet1 SUM

Cell(A2) - Sheet2 SUM

Cell(A3) - Sheet3 SUMI have tried the below code but its not sum every nth value and its giving the first

cell value only. Please help with the codingSub everynthvalue() Dim J As Integer Dim R As Integer amount = 0 With Worksheets("Sourced") For Each ws In ThisWorkbook.Worksheets If ws.Name <> "Sourced" Then For J = 1 To 5000 If IsEmpty(Cells(J, 1)) = True And _ IsEmpty(Cells(J + 1, 1)) = True Then For R = 26 To 10000 If ws.Cells(R, 9).Row Mod 1 = 0 Then amount = amount + ws.Cells(R, 9).Value Exit For End If Next R .Cells(J, 1) = amount Exit For End If Next J End If Next ws End WithRegards

✔ Best Answer

Actually, VBA is not required. I'm not saying that it can't be used, just that it is not required. I will look at your VBA when I have a chance, but for now, consider this solution.With a list of worksheet names in a Column (e.g. B) a formula that includes the INDIRECT function would work.

Using the SUMPRODUCT example at the site that I suggested and modifying it to include INDIRECT function to read the sheet names from Column B, this example would SUM every 8th cell in the referenced range in each sheet as you drag it down Column A:

=SUMPRODUCT((MOD(ROW(INDIRECT(B1&"!$I$26:$I$10000")),8)=0)*(INDIRECT(B1&"!$I$26:$I$10000")))

As for your code, perhaps you could eliminate the MOD function and use Step 8 in your loop to sum every 8th cell.For R = 26 To 10000 Step 8 amount = amount + Cells(R, 9) NextLots of choices, I just don't have access to VBA at this time.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

I don't have time to look at your code right now, but I'll toss this out. Would a formula based solution work for you? A few options can be found here...

https://www.extendoffice.com/docume...

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Thanks for your reply Derby !

I have around 40 to 50 Sheets. so VBA Code is required to sum the cells. Appreciate if you take a look on the code.

Regards

As a possible work around that uses a UDF and one Formula, try this: Go to the link suggested by DerbyDad, there you will find a UDF about half way down the page.

insert the UDF in your workbook.In the

SAME CELL on each worksheet use the UDF to SUM the range in each worksheet.On your Master sheet, in the cell where you want the Sum of Sums enter the formula:

=SUM(Sheet1:Sheet50!XX1)

If your sheets are named something other than Sheet1, Sheet2, etc

the formula is:=SUM( FirstSheet : LastSheet ! Cell )

You could also use one of the formulas suggested and get the same result.

See how that works for you.

MIKE

Actually, VBA is not required. I'm not saying that it can't be used, just that it is not required. I will look at your VBA when I have a chance, but for now, consider this solution.With a list of worksheet names in a Column (e.g. B) a formula that includes the INDIRECT function would work.

Using the SUMPRODUCT example at the site that I suggested and modifying it to include INDIRECT function to read the sheet names from Column B, this example would SUM every 8th cell in the referenced range in each sheet as you drag it down Column A:

=SUMPRODUCT((MOD(ROW(INDIRECT(B1&"!$I$26:$I$10000")),8)=0)*(INDIRECT(B1&"!$I$26:$I$10000")))

As for your code, perhaps you could eliminate the MOD function and use Step 8 in your loop to sum every 8th cell.For R = 26 To 10000 Step 8 amount = amount + Cells(R, 9) NextLots of choices, I just don't have access to VBA at this time.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Thanks Debry, I made small changes like u said, its working fine. the below is the code.

Sub Macro1() Dim i As Integer Dim j As Integer Dim total As Double With Worksheets("Sourced") For Each ws In ThisWorkbook.Worksheets If ws.Name <> "Sourced" Then For i = 1 To 5000 If IsEmpty(Cells(i, 1)) = True And IsEmpty(Cells(i + 1, 1)) = True Then total = 0 For j = 5 To 16 Step 5 total = total + ws.Cells(j, 1) Next .Cells(i, 1) = total Exit For End If Next i End If Next ws End With End Sub

message edited by jarlamiv

I'm glad you got it working. Quick question: Is the Sourced workbook the first or last sheet in the workbook?

If so, I think you can make your code a little more efficient by using something like these:

For ws_num = 2 To Sheets.Countor

For ws_num = 1 To Sheets.Count - 1Your method requires VBA to check the sheet name 40-50 times and compare it to "Sourced" each time. In this case, it would be a minimal improvement in efficiency, but each improvement adds up as your code gets more and more complicated.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Ask Your Question

Weekly Poll

Do you think state sales tax should be charged in the United States on all Internet sales?

Discuss in The Lounge

Poll History