Forum Discussion

Excel's avatar
Excel
Iron Contributor
Sep 03, 2022

Autosum VBA

Hello Everyone, 

 

I have seen the autosum, then it worked. 

 

But whenever i added data, the all TOTAL are repeated in all sheets..

 

 

like :

 

 

So, what should i add in VBA code ?

 

Please help..

 

Here is a attached file

Macros and VBA

5 Replies

Sort By
    • Excel's avatar
      Excel
      Iron Contributor
      Sep 03, 2022

      HansVogelaar 

       

      Please check the VBA code :

       

       

      Sub CreateRowSums()

      Dim wsh As Worksheet

      Dim lr As Long

      Dim lc As Long

      Dim LastRow As Long

      Application.ScreenUpdating = False

      For Each wsh In Worksheets

      lr = wsh.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

      lc = wsh.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

      wsh.Cells(2, lc).Resize(lr - 1).FormulaR1C1 = "=SUM(RC3:RC[-1])"

      LastRow = wsh.Range("F1").End(xlDown).Row



      If Not ActiveCell.HasFormula Then

      wsh.Range("F" & LastRow + 1).Formula = "=SUM(F2:F" & LastRow & ")"

      wsh.Range("C" & LastRow + 1).Formula = "=SUM(C2:C" & LastRow & ")"

      wsh.Range("D" & LastRow + 1).Formula = "=SUM(D2:D" & LastRow & ")"

      wsh.Range("E" & LastRow + 1).Formula = "=SUM(E2:E" & LastRow & ")"

      End If

      Next wsh

      Application.ScreenUpdating = True

      End Sub

       

       

       

      after run the code, total is not repititive but when i insert total are not coming.

       

      Sir, if you write VBA code, then it can easily understandable. Please help..?

       

      Here is a updated attached file..

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Sep 03, 2022

        Excel 

        You're not really trying, are you?

        Sub CreateRowSums()
    Dim wsh As Worksheet
    Dim lr As Long
    Dim lc As Long
    Dim LastRow As Long
    Application.ScreenUpdating = False
    For Each wsh In Worksheets
        lr = wsh.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        lc = wsh.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        wsh.Cells(2, lc).Resize(lr - 1).FormulaR1C1 = "=SUM(RC3:RC[-1])"
        If Not wsh.Cells(lr, 3).HasFormula Then
            wsh.Cells(lr + 1, 3).Resize(, lc - 2).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
        End If
        wsh.UsedRange.EntireColumn.AutoFit
    Next wsh
    Application.ScreenUpdating = True
End Sub

Resources