Hello
I'm not sure how explain this but I'm in real trouble how avoid repeating sum of numbers are already collected .
so every sheet contains TOTAL row when I merge across sheets and show the result in list sheet will sum also TOTAL row for each sheet , this is wrong becuase TOTAL row has alreay sum values above . so what I want when I sum across sheets should exclude TOTAL row from summing when show the result for each sheet.
the code wiil populate sheets names in column B and sum the column C,D for each sheet and insert total row to sum whole sheets in LIST sheet . this is ok , but I need exclude TOTAL row from the others sheets from operation summing until doesn't repeat summing and gives error in result .
Sub test()
Dim ws As Worksheet, Cnt As Long, Impt As Double, Expt As Double, Bal As Double, Lr As Long
Sheets("LIST").[A1].CurrentRegion.Offset(1).Clear
For Each ws In Sheets
If ws.Index < Sheets("LIST").Index Then
With ws.[A1].CurrentRegion
Impt = Application.Sum(.Columns(3))
Expt = Application.Sum(.Columns(4))
Bal = Impt - Expt: Cnt = Cnt + 1
End With
With Sheets("LIST").Range("A" & Rows.Count).End(3)(2)
.Resize(, 5) = Array(Cnt, ws.Name, Impt, Expt, Bal)
End With
End If
Next
With Sheets("LIST")
Lr = .Range("B" & Rows.Count).End(3).Row + 1
.Cells(Lr, 2) = "TOTAL"
.Cells(Lr, 3).Resize(, 3) = "=Sum(C2:C" & Lr - 1 & ")"
With .[A1].CurrentRegion
.Borders.LineStyle = 1
.Columns(3).Resize(, 3).NumberFormat = "#,0.00"
.HorizontalAlignment = xlCenter
End With
End With
End Sub