Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

exclude TOTAL row when I sum two columns across sheets

0

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
Answer
Discuss

Answers

0
Selected Answer

Hi again Malkal

In the attached revised file, I added a nested With/ End With block so that the last row of the Current region is excluded before the Sum function is applied- see changes in bold in the code extract below:

   If ws.Index < Sheets("LIST").Index Then
      With ws.[A1].CurrentRegion
        ' remove last row from range
        With .Rows("1:" & .Rows.Count - 1)
           Impt = Application.Sum(.Columns(3))
           Expt = Application.Sum(.Columns(4))
           Bal = Impt - Expt: Cnt = Cnt + 1
        End With
    End With

Now the correct totals are "merged" into worksheet LIST (on which I also took the liberty of correcting the headers).

Hope this fixes things for you.

Discuss

Discussion

thanks  very  much, John .
Malkal (rep: 22) Jul 10, '23 at 4:48 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login