ActiveSheet.ListObjects("Table_pastel_12_cust_rank").ListColumns("2019-032"). _
TotalsCalculation = xlTotalsCalculationSum
Hi there
below is my macrofrom a sheet where i import data from mysql database. the field names are not always the same and keeps changing. thus above example will not help me.
I need something like to make the calculation - show totals auto sum for column c.
ActiveSheet.ListObjects("$C").ListColumns("$C"). _
TotalsCalculation = xlTotalsCalculationSum
Code
- Sub Accounts_macro()
- '
- ' Accounts_macro Macro
- '
- '
- Columns("C:CE").Select
- Selection.Delete Shift:=xlToLeft
- Range("B4").Select
- Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
- Columns("E:E").Select
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Range("E4").Select
- ActiveCell.FormulaR1C1 = "=R[-2]C[-1]-R[-2]C[-2]"
- Range("F4").Select
- Columns("F:F").Select
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Range("F4").Select
- ActiveCell.FormulaR1C1 = "=(RC[-2]-RC[-3])/RC[-2]"
- Range("E4").Select
- ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
- Columns("H:H").Select
- Columns("I:I").Select
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Range("I4").Select
- ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
- Columns("J:J").Select
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Range("J4").Select
- ActiveCell.FormulaR1C1 = "(h4-g4)/h4"
- Range("J4").Select
- ActiveCell.FormulaR1C1 = "=(RC[-2]-RC[-3])/RC[-2]"
- Range("J5").Select
- Columns("J:J").ColumnWidth = 8.86
- Columns("M:M").Select
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Range("M4").Select
- ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
- Range("N4").Select
- ActiveCell.FormulaR1C1 = "=(RC[-2]-RC[-3])/RC[-2]"
- Range("N5").Select
- ActiveWindow.SmallScroll ToRight:=1
- Columns("P:P").EntireColumn.AutoFit
- Range("N5").Select
- Selection.AutoFill Destination:=Range("N4:N5"), Type:=xlFillDefault
- Range("N4:N5").Select
- Columns("N:N").Select
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Range("N4").Select
- ActiveCell.FormulaR1C1 = "=(RC[-2]-RC[-3])/RC[-2]"
- Columns("Q:Q").Select
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Range("Q4").Select
- ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
- Columns("R:R").Select
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Range("R4").Select
- ActiveCell.FormulaR1C1 = "=(RC[-2]-RC[-3])/RC[-2]"
- Range("R5").Select
- ActiveWindow.ScrollColumn = 4
- ActiveWindow.ScrollColumn = 5
- ActiveWindow.ScrollColumn = 6
- ActiveWindow.ScrollColumn = 7
- ActiveWindow.ScrollColumn = 8
- ActiveWindow.ScrollColumn = 9
- ActiveWindow.ScrollColumn = 10
- ActiveWindow.ScrollColumn = 11
- ActiveWindow.ScrollColumn = 12
- ActiveWindow.ScrollColumn = 13
- ActiveWindow.ScrollColumn = 14
- ActiveWindow.ScrollColumn = 15
- ActiveWindow.ScrollColumn = 16
- ActiveWindow.ScrollColumn = 17
- Columns("U:U").Select
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Range("U4").Select
- Columns("T:T").EntireColumn.AutoFit
- Columns("S:S").EntireColumn.AutoFit
- Range("U4").Select
- ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
- Range("V4").Select
- ActiveCell.FormulaR1C1 = "=(RC[-2]-RC[-3])/RC[-2]"
- Columns("Y:Y").Select
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Range("Y4").Select
- ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
- Columns("Z:Z").Select
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Range("Z4").Select
- ActiveCell.FormulaR1C1 = "=(RC[-2]-RC[-3])/RC[-2]"
- Columns("AC:AC").Select
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Range("AC4").Select
- ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
- Columns("AD:AD").Select
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Range("AD4").Select
- ActiveCell.FormulaR1C1 = "=(RC[-2]-RC[-3])/RC[-2]"
- Range("AD5").Select
- ActiveWindow.SmallScroll ToRight:=6
- Columns("AG:AG").Select
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Range("AG4").Select
- ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
- Range("AH4").Select
- Columns("AH:AH").Select
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Range("AH4").Select
- ActiveCell.FormulaR1C1 = "=(RC[-2]-RC[-3])/RC[-2]"
- Columns("AK:AK").Select
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Range("AK4").Select
- ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
- Columns("AL:AL").Select
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Range("AL4").Select
- ActiveCell.FormulaR1C1 = "=(RC[-2]-RC[-3])/RC[-2]"
- Range("AL5").Select
- ActiveWindow.SmallScroll ToRight:=7
- Columns("AO:AO").Select
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Range("AO4").Select
- ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
- Columns("AP:AP").Select
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Range("AP4").Select
- ActiveCell.FormulaR1C1 = "=(RC[-2]-RC[-3])/RC[-3]"
- Columns("AS:AS").Select
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Range("AS4").Select
- ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
- Columns("AT:AT").Select
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Range("AT4").Select
- ActiveCell.FormulaR1C1 = "=(RC[-2]-RC[-3])/RC[-2]"
- Range("AT5").Select
- ActiveWindow.SmallScroll ToRight:=6
- Range("Table_pastel_12_cust_rank[[#Headers],[2019-02]]").Select
- Range("AV5").Select
- ActiveWindow.SmallScroll ToRight:=7
- Columns("Aw:Aw").Select
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Range("Aw4").Select
- ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
- Columns("Ax:Ax").Select
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Range("Ax4").Select
- ActiveCell.FormulaR1C1 = "=(RC[-2]-RC[-3])/RC[-3]"
- Range("AY4").Select
- ActiveCell.FormulaR1C1 = _
- "=RC[-48]+RC[-44]+RC[-40]+RC[-36]+RC[-32]+RC[-28]+RC[-24]+RC[-20]+RC[-16]+RC[-12]+RC[-8]+RC[-4]"
- Range("AZ4").Select
- ActiveCell.FormulaR1C1 = _
- "=RC[-48]+RC[-40]+RC[-36]+RC[-32]+RC[-28]+RC[-24]+RC[-20]+RC[-16]+RC[-12]+RC[-8]+RC[-4]"
- Range("Table_pastel_12_cust_rank[[#Totals],[2019-033]]").Select
- ActiveWindow.ScrollColumn = 2
- ActiveWindow.ScrollColumn = 20
- ActiveWindow.ScrollColumn = 31
- ActiveWindow.ScrollColumn = 39
- ActiveWindow.ScrollColumn = 43
- ActiveWindow.ScrollColumn = 52
- ActiveWindow.ScrollColumn = 54
- ActiveWindow.ScrollColumn = 57
- ActiveWindow.ScrollColumn = 71
- ActiveWindow.ScrollColumn = 73
- ActiveWindow.ScrollColumn = 72
- ActiveWindow.ScrollColumn = 70
- ActiveWindow.ScrollColumn = 68
- ActiveWindow.ScrollColumn = 65
- ActiveWindow.ScrollColumn = 56
- ActiveWindow.ScrollColumn = 54
- ActiveWindow.ScrollColumn = 53
- ActiveWindow.ScrollColumn = 49
- ActiveWindow.ScrollColumn = 48
- ActiveWindow.ScrollColumn = 47
- ActiveWindow.ScrollColumn = 45
- ActiveWindow.ScrollColumn = 44
- ActiveWindow.ScrollColumn = 43
- ActiveWindow.ScrollColumn = 42
- ActiveWindow.ScrollColumn = 41
- ActiveWindow.ScrollColumn = 42
- ActiveSheet.ListObjects("Table_pastel_12_cust_rank").ShowTotals = False
- ActiveSheet.ListObjects("Table_pastel_12_cust_rank").ShowTotals = True
- End Sub