Code to calculate totals needs to take into account previous totals

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,007
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have a spreadsheet relating to various periods on one sheet. There is a button to add a period and when it is pressed it copies the 20 rows of calculations and pastes them below the bottom row.

Each period has 2 tables for calculations and totals. At the bottom of the periods, I want a grand total. The grand total is to appear under the bottom instance of the tables and totals, depending on how many periods have been added. For the first period:
  • H32 contains the total for the period
  • H33 contains the grand total
When the Addrows code is run, I need the new grand total to be a sum of the cell directly above it, which is the total for the last period and the cell that is 20 rows above it in the same row, which is the previous grand total.

This is my code. It worked fine until I tried to add the above feature with the last line of code.


VBA Code:
Sub AddRows()
    Dim Total As Range
    Dim WS As Worksheet
    Dim Sh As Shape, NewShape As Shape
    Dim TTop As Long
    Dim TLeft As Long

    Set WS = ThisWorkbook.Worksheets("ACA_Quoting")


    For Each Sh In WS.Shapes
        Select Case Sh.Name
        Case "cmdAddRatio", "cmdCustomSign", "cmdGsign", "cmdNoSign", "cmdSaveToPdf"
            Sh.IncrementTop 472.5
        Case "txtMain"                                'name your first textbox, the one you want to move,  to something unique. I used "txtMain"
            TTop = Sh.Top                             'record position
            TLeft = Sh.Left
            Sh.IncrementTop 472.5                     'move it
            Sh.Copy                                   'make a copy
            WS.Paste
            Set NewShape = WS.Shapes(WS.Shapes.Count)    'pasted textbox is the last shape
            With NewShape
                .Top = TTop                           'move the copy to the previous position of txtMain
                .Left = TLeft
            End With
        End Select
    Next Sh
   
    With WS
        .Range("A8:V33").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(10, 0) 'Pastes a copy of the table below current table between the bottom of the table and the totals
        .Cells(.Rows.Count, "H").End(xlUp).Formula = .Cells(.Rows.Count, "H").End(xlUp).Formula & "+" & .Cells(.Rows.Count, "H").End(xlUp).Offset(10, 0).Formula
    End With
End Sub

When I try and run this code I get the error Application defined or object defined error. How do I fix it so it inserts the correct formula?

Thanks
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,703
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
I might be missing the point, but why don't you simply sum the entire column and then divide by 2
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,007
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
As each period is different. I am only copying the layout. If there is any data in the tables, it will be changed.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,473
Messages
5,596,356
Members
414,061
Latest member
JJSB

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top